داشبورد الحضور: صفحة واحدة

• LinkID: مفتاح الربط
• TenureYears: من تاريخ التعبئة
• IDDaysLeft / IDStatus / IDSort: انتهاء الهوية (سالب = منتهية، ≤60، ≤90، Valid، No date)
• HasMaadenID، AreaClean، CategoryClean، NationalityClean: الفاضي يصير نصًا واضحًا بدل (Blank)
• وبعدها 5 أعمدة DAX محسوبة (في القسم 3)

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\02_Attendance\\3_Output\\PAP_Mech_Employee_Master.xlsx"), null, true),
    T = Source{[Item="tblEmployees", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"MaadenID", "SIS_ID", "EmployeeName", "Craft", "Area", "Category", "ContractType", "EmploymentStatus", "Nationality", "Type of ID", "ID Expire Date", "JoiningDate", "ExitDate", "WorkingDays", "WorkingHours", "DefaultShift", "Note"}),
    NoBlank = Table.SelectRows(Kept, each [EmployeeName] <> null and [EmployeeName] <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"MaadenID", type text}, {"SIS_ID", type text}, {"EmployeeName", type text}, {"Craft", type text}, {"Area", type text}, {"Category", type text}, {"ContractType", type text}, {"EmploymentStatus", type text}, {"Nationality", type text}, {"Type of ID", type text}, {"ID Expire Date", type date}, {"JoiningDate", type date}, {"ExitDate", type date}, {"WorkingDays", Int64.Type}, {"WorkingHours", Int64.Type}, {"DefaultShift", type text}, {"Note", type text}}),
    Today = Date.From(DateTime.LocalNow()),
    // مفتاح الربط مع ملف الحضور: رقم السويدي للمقاول، ورقم معادن للمباشر
    A1 = Table.AddColumn(Typed, "LinkID", each if [SIS_ID] <> null and Text.Trim([SIS_ID]) <> "" then Text.Trim([SIS_ID]) else Text.Trim([MaadenID]), type text),
    A2 = Table.AddColumn(A1, "TenureYears", each if [JoiningDate] = null then null else Number.Round(Duration.Days(Today - [JoiningDate]) / 365.25, 1), type number),
    A3 = Table.AddColumn(A2, "IDDaysLeft", each if [ID Expire Date] = null then null else Duration.Days([ID Expire Date] - Today), Int64.Type),
    A4 = Table.AddColumn(A3, "IDStatus", each if [ID Expire Date] = null then "No date" else if [IDDaysLeft] < 0 then "Expired" else if [IDDaysLeft] <= 60 then "Due in 60 days" else if [IDDaysLeft] <= 90 then "Due in 90 days" else "Valid", type text),
    A5 = Table.AddColumn(A4, "IDSort", each if [IDStatus] = "Expired" then 1 else if [IDStatus] = "Due in 60 days" then 2 else if [IDStatus] = "Due in 90 days" then 3 else if [IDStatus] = "Valid" then 4 else 5, Int64.Type),
    A6 = Table.AddColumn(A5, "HasMaadenID", each if [MaadenID] = null or Text.Trim([MaadenID]) = "" then "Missing" else "Yes", type text),
    A7 = Table.AddColumn(A6, "AreaClean", each if [Area] = null or Text.Trim([Area]) = "" then "(No area)" else [Area], type text),
    A8 = Table.AddColumn(A7, "CategoryClean", each if [Category] = null or Text.Trim([Category]) = "" then "(No category)" else [Category], type text),
    A9 = Table.AddColumn(A8, "NationalityClean", each if [Nationality] = null or Text.Trim([Nationality]) = "" then "(Not on contractor list)" else [Nationality], type text)
in
    A9

• Merge مع Employees على LinkID (نفس فكرة XLOOKUP) يجيب DefaultShift
• Shift: Day/Night كما كُتب، أو On Duty ← الشفت الافتراضي
• Presence: On site / Absent / Sick leave / Vacation / Day off / Permission / Exit
• OnSite: 1 أو 0 للجمع
• InMaster: هل الرقم موجود في الماستر

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\02_Attendance\\0_Source\\Daily_Attendance_Copy_2026-09-14.xlsm"), null, true),
    T = Source{[Item="AttendanceTracker", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Day", "EmployeeID", "EmployeeName", "Status"}),
    NoBlank = Table.SelectRows(Kept, each [EmployeeID] <> null and Text.From([EmployeeID]) <> "" and [Date] <> null),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Day", type text}, {"EmployeeID", type text}, {"EmployeeName", type text}, {"Status", type text}}),
    Trim = Table.TransformColumns(Typed, {{"EmployeeID", Text.Trim, type text}, {"Status", Text.Trim, type text}}),
    // الشفت الافتراضي يجي من ملف الموظفين (Merge = XLOOKUP)
    J = Table.NestedJoin(Trim, {"EmployeeID"}, Employees, {"LinkID"}, "E", JoinKind.LeftOuter),
    X = Table.ExpandTableColumn(J, "E", {"DefaultShift", "LinkID"}, {"DefaultShift", "MatchedID"}),
    B1 = Table.AddColumn(X, "Shift", each if [Status] = "Day" or [Status] = "Night" then [Status] else if [Status] = "On Duty" then (if [DefaultShift] = null then "Day" else [DefaultShift]) else null, type text),
    B2 = Table.AddColumn(B1, "Presence", each if [Shift] <> null then "On site" else if [Status] = "Absent" then "Absent" else if [Status] = "Sick Leave" then "Sick leave" else if [Status] = "Vacation" then "Vacation" else if [Status] = "Off" then "Day off" else if [Status] = "Permission" then "Permission" else if [Status] = "Exit" then "Exit" else "Other", type text),
    B3 = Table.AddColumn(B2, "PresenceSort", each if [Presence] = "On site" then 1 else if [Presence] = "Absent" then 2 else if [Presence] = "Sick leave" then 3 else if [Presence] = "Permission" then 4 else if [Presence] = "Vacation" then 5 else if [Presence] = "Day off" then 6 else if [Presence] = "Exit" then 7 else 8, Int64.Type),
    B4 = Table.AddColumn(B3, "OnSite", each if [Shift] <> null then 1 else 0, Int64.Type),
    B5 = Table.AddColumn(B4, "InMaster", each if [MatchedID] = null then "Not in master" else "Yes", type text),
    Out = Table.RemoveColumns(B5, {"MatchedID"})
in
    Out

• إعادة تسمية الأعمدة لأسماء مفهومة
• SIS كنص (عشان يطابق)
• التواريخ النصية مثل 0-Jan-00 تصير فاضية بدل ما تكسر التحديث (try … otherwise null)
• Status و Nationality بـ Text.Proper

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\02_Attendance\\0_Source\\Suwaidi_MasterSheet_2026-09-14.xlsx"), null, true),
    T = Source{[Item="Table7", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"SIS FUSION NUMBER", "MPC ID #", "MPC ID EXPIRY DATE", "NAME", "UTILIZATION TRADE", "STATUS", "MOBILIZED DATE", "DMOBD", "REASON FOR DMOB", "VAC /EXIT DATE", "NATIONALITY", "REMARKS"}),
    Renamed = Table.RenameColumns(Kept, {{"SIS FUSION NUMBER", "SIS"}, {"MPC ID #", "Maaden ID (contractor)"}, {"MPC ID EXPIRY DATE", "ID expiry (contractor)"}, {"NAME", "Name"}, {"UTILIZATION TRADE", "Trade"}, {"STATUS", "Status"}, {"MOBILIZED DATE", "Mobilized"}, {"DMOBD", "DMOB date"}, {"REASON FOR DMOB", "DMOB reason"}, {"VAC /EXIT DATE", "Vac/Exit date"}, {"NATIONALITY", "Nationality"}, {"REMARKS", "Remarks"}}),
    NoBlank = Table.SelectRows(Renamed, each [SIS] <> null and Text.From([SIS]) <> ""),
    AsText = Table.TransformColumns(NoBlank, {{"SIS", each Text.Trim(Text.From(_)), type text}, {"Maaden ID (contractor)", each if _ = null then null else Text.Trim(Text.From(_)), type text}}),
    // التاريخ اللي يوصل نص مثل 0-Jan-00 يصير فاضي بدل ما يكسر التحديث
    Dates = Table.TransformColumns(AsText, {{"ID expiry (contractor)", each try Date.From(_) otherwise null, type date}, {"Mobilized", each try Date.From(_) otherwise null, type date}, {"DMOB date", each try Date.From(_) otherwise null, type date}, {"Vac/Exit date", each try Date.From(_) otherwise null, type date}}),
    Proper = Table.TransformColumns(Dates, {{"Status", each Text.Proper(Text.Trim(Text.From(_))), type text}, {"Nationality", each if _ = null then null else Text.Proper(Text.Trim(Text.From(_))), type text}, {"Name", each if _ = null then null else Text.Proper(Text.Trim(Text.From(_))), type text}}),
    Typed = Table.TransformColumnTypes(Proper, {{"Trade", type text}, {"DMOB reason", type text}, {"Remarks", type text}})
in
    Typed

• من 1 أغسطس 2026 لسنة ونص
• DayName مرتب بالأحد أولًا، Week، MonthName، IsFriSat

let
    D0 = #date(2026, 8, 1),
    Days = List.Dates(D0, 517, #duration(1, 0, 0, 0)),
    T = Table.FromList(Days, Splitter.SplitByNothing(), {"Date"}),
    T1 = Table.TransformColumnTypes(T, {{"Date", type date}}),
    T2 = Table.AddColumn(T1, "DayName", each Date.ToText([Date], "ddd"), type text),
    T3 = Table.AddColumn(T2, "DaySort", each Date.DayOfWeek([Date], Day.Sunday), Int64.Type),
    T4 = Table.AddColumn(T3, "WeekStart", each Date.StartOfWeek([Date], Day.Sunday), type date),
    T5 = Table.AddColumn(T4, "Week", each "W" & Text.From(Date.WeekOfYear([Date], Day.Sunday)), type text),
    T6 = Table.AddColumn(T5, "WeekSort", each Date.Year([Date]) * 100 + Date.WeekOfYear([Date], Day.Sunday), Int64.Type),
    T7 = Table.AddColumn(T6, "MonthName", each Date.ToText([Date], "MMM yyyy"), type text),
    T8 = Table.AddColumn(T7, "MonthSort", each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type),
    T9 = Table.AddColumn(T8, "IsFriSat", each if Date.DayOfWeek([Date], Day.Sunday) >= 5 then "Fri/Sat" else "Workday", type text)
in
    T9

COUNTROWS ( Attendance ) + 0

SUM ( Attendance[OnSite] ) + 0

CALCULATE ( [On Site], Attendance[Shift] = "Day" )

CALCULATE ( [On Site], Attendance[Shift] = "Night" )

CALCULATE ( [Records], Attendance[Presence] = "Absent" )

CALCULATE ( [Records], Attendance[Presence] = "Sick leave" )

CALCULATE ( [Records], Attendance[Presence] = "Vacation" )

CALCULATE ( [Records], Attendance[Presence] = "Day off" )

CALCULATE ( [Records], Attendance[Presence] = "Permission" )

CALCULATE ( [Records], Attendance[Presence] = "Exit" )

DISTINCTCOUNT ( Attendance[EmployeeID] ) + 0

CALCULATE ( DISTINCTCOUNT ( Attendance[Date] ) ) + 0

CALCULATE ( COUNTROWS ( Employees ), Employees[EmploymentStatus] = "Active" ) + 0

[Active Headcount] * [Days In View]

MAX ( 0, [Expected Records] - [Records] )

[On Site]

MAX ( 0, [Records] - [Att Day off] - [Att Exit] )

CALCULATE ( DISTINCTCOUNT ( Attendance[Date] ), REMOVEFILTERS ( Employees ), REMOVEFILTERS ( Attendance[EmployeeID] ) ) + 0

IF ( SELECTEDVALUE ( Employees[EmploymentStatus] ) = "Active", MAX ( 0, [Days In View All] - [Records] ), 0 )

[Att Absent] * 3 + [Att Sick leave] * 2 + [Missed Days]

DIVIDE ( [Records], [Expected Records] )

DIVIDE ( [On Site], [Records] )

DIVIDE ( [Att Absent] + [Att Sick leave], [Records] )

DIVIDE ( [Att Vacation] + [Att Day off], [Records] )

DIVIDE ( [Night Shift], [On Site] )

DIVIDE ( [On Site], [Days In View] )

MAX ( Attendance[Date] )

1

1

DIVIDE ( [On Site], [Scheduled Days] )

DIVIDE ( [Att Absent] + [Att Sick leave], [Scheduled Days] )

DIVIDE ( [Att Vacation] + [Att Permission], [Scheduled Days] )

0.95

0.015

VAR n = [Days In View] VAR d1 = MIN ( Attendance[Date] ) RETURN IF ( n > 0, CALCULATE ( [Attendance Rate %], DATESBETWEEN ( Calendar[Date], d1 - n, d1 - 1 ) ) )

VAR n = [Days In View] VAR d1 = MIN ( Attendance[Date] ) RETURN IF ( n > 0, CALCULATE ( [Absenteeism %], DATESBETWEEN ( Calendar[Date], d1 - n, d1 - 1 ) ) )

CALCULATE ( COUNTROWS ( Employees ), Employees[EmploymentStatus] IN { "Active", "Vacation" } ) + 0

CALCULATE ( COUNTROWS ( Employees ), Employees[EmploymentStatus] = "Vacation" ) + 0

CALCULATE ( COUNTROWS ( Employees ), Employees[EmploymentStatus] IN { "Exit", "Resigned", "Demobilized" } ) + 0

CALCULATE ( [Headcount], Employees[ContractType] = "Direct" )

CALCULATE ( [Headcount], Employees[ContractType] = "Subcontract" )

DIVIDE ( CALCULATE ( [Headcount], Employees[Type of ID] IN { "Permanent", "Permanent (Old ID)", "Maaden Staff" } ), [Headcount] )

CALCULATE ( [Headcount], Employees[Type of ID] = "TAM" )

AVERAGE ( Employees[TenureYears] )

CALCULATE ( [Headcount], Employees[DefaultShift] = "Night" )

CALCULATE ( COUNTROWS ( Employees ), Employees[HasMaadenID] = "Missing" ) + 0

CALCULATE ( [Headcount], Employees[IDStatus] = "Expired" )

CALCULATE ( [Headcount], Employees[IDStatus] = "Due in 60 days" )

CALCULATE ( [Headcount], Employees[IDStatus] = "Due in 90 days" )

CALCULATE ( [Headcount], Employees[IDStatus] = "No date" )

COUNTROWS ( Employees ) + 0

COUNTROWS ( Contractor ) + 0

CALCULATE ( COUNTROWS ( Contractor ), Contractor[InMaster] = "New: not in our master" ) + 0

CALCULATE ( COUNTROWS ( Employees ), Employees[InContractorList] = "No" ) + 0

CALCULATE ( COUNTROWS ( Employees ), LEFT ( Employees[StatusMatch], 8 ) = "Mismatch", Employees[EmploymentStatus] IN { "Active", "Vacation" } ) + 0

CALCULATE ( DISTINCTCOUNT ( Attendance[EmployeeID] ), Attendance[InMaster] = "Not in master" ) + 0

CALCULATE ( COUNTROWS ( Employees ), Employees[AreaClean] = "(No area)" || Employees[CategoryClean] = "(No category)" ) + 0

"Last day recorded " & FORMAT ( [Last Recorded], "dd MMM yyyy" ) & " · " & FORMAT ( [Active Headcount], "#,0" ) & " active employees"

"Day " & FORMAT ( [Day Shift], "#,0" ) & " · night " & FORMAT ( [Night Shift], "#,0" )

"Recorded " & FORMAT ( [Records], "#,0" ) & " of " & FORMAT ( [Expected Records], "#,0" ) & " expected"

"Absent " & FORMAT ( [Att Absent], "#,0" ) & " · sick " & FORMAT ( [Att Sick leave], "#,0" )

"Vacation " & FORMAT ( [Att Vacation], "#,0" ) & " · off " & FORMAT ( [Att Day off], "#,0" )

"Across " & FORMAT ( [Days In View], "#,0" ) & " day(s) in the filter"

"Distinct people seen " & FORMAT ( [People Seen], "#,0" )

"Direct " & FORMAT ( [Direct Staff], "#,0" ) & " · subcontract " & FORMAT ( [Subcontract Staff], "#,0" )

"Exited or moved " & FORMAT ( [Exited], "#,0" )

"TAM " & FORMAT ( [TAM Count], "#,0" ) & " · night default " & FORMAT ( [Night Default], "#,0" )

"Years since mobilisation, average"

"No expiry date on file: " & FORMAT ( [IDs No Date], "#,0" )

"Cannot be linked to attendance"

"On Al Suwaidi list, missing from our master"

"Subcontract in our master, not on their list"

"Our status vs contractor status"

"Numbers typed in attendance with no employee"

VAR d0 = MIN ( Attendance[Date] ) VAR d1 = MAX ( Attendance[Date] ) RETURN IF ( ISBLANK ( d0 ), "no records", IF ( d0 = d1, FORMAT ( d0, "dd MMM yyyy" ), FORMAT ( d0, "dd MMM" ) & " to " & FORMAT ( d1, "dd MMM yyyy" ) ) )

"Attendance " & [Period Label] & ": " & FORMAT ( [Attendance Rate %], "0.0%" ) & " of scheduled days on site, target 95%"

VAR p = [Attendance Rate Prev] VAR d = [Attendance Rate %] - p RETURN IF ( ISBLANK ( p ), "No previous period to compare", IF ( d >= 0, UNICHAR ( 9650 ), UNICHAR ( 9660 ) ) & " " & FORMAT ( ABS ( d ) * 100, "0.0" ) & " pp vs previous " & FORMAT ( [Days In View], "#,0" ) & " days" )

VAR p = [Absenteeism Prev] VAR d = [Absenteeism %] - p RETURN "Absent " & FORMAT ( [Att Absent], "#,0" ) & " · sick " & FORMAT ( [Att Sick leave], "#,0" ) & IF ( ISBLANK ( p ), "", " · " & IF ( d > 0, UNICHAR ( 9650 ), UNICHAR ( 9660 ) ) & " " & FORMAT ( ABS ( d ) * 100, "0.0" ) & " pp" )

"Vacation " & FORMAT ( [Att Vacation], "#,0" ) & " · permission " & FORMAT ( [Att Permission], "#,0" )

"Recorded " & FORMAT ( [Records], "#,0" ) & " of " & FORMAT ( [Expected Records], "#,0" ) & " · missing " & FORMAT ( [Not Recorded], "#,0" )

"Last day recorded " & FORMAT ( [Last Recorded], "dd MMM yyyy" ) & " · " & FORMAT ( [Active Headcount], "#,0" ) & " active · " & FORMAT ( [Scheduled Days], "#,0" ) & " scheduled days"

VAR r = [Attendance Rate %] RETURN SWITCH ( TRUE (), ISBLANK ( r ), "#FFFFFF", r >= 0.95, "#B8A567", r >= 0.85, "#D9CFA3", "#F1EACB" )