دليل بناء Maintenance Hub

IsClosed

1 إذا الحالة Closed. تستخدمه المقاييس والتلوين.

Days open

من تاريخ الرصد إلى الإغلاق، أو إلى آخر تاريخ رصد إذا للحين مفتوح.

Overdue

1 إذا مو مقفل وتاريخ الهدف فات.

Closed on time

1 إذا انقفل قبل أو في تاريخ الهدف.

Age bucket / AgeSort

فئات العمر: 0-7، 8-14، 15-30، 31-60، 60+ يوم، مع رقم للترتيب.

Month / MonthSort

الشهر كنص (Sep 26) ورقم للترتيب (202609).

SeveritySort

Critical=1 … Low=4 عشان الترتيب يكون منطقي مو أبجدي.

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblHEMAYA_Log", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"ID", "Reported", "Area", "Location", "Equipment", "Type", "Category", "Severity", "What happened", "Root cause (why)", "Immediate action", "Corrective action (how closed)", "Raised by", "Owner", "Target date", "Closed date", "Status", "Verified by", "Source"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "ID") <> null and Record.Field(_, "ID") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"ID", type text}, {"Reported", type date}, {"Area", type text}, {"Location", type text}, {"Equipment", type text}, {"Type", type text}, {"Category", type text}, {"Severity", type text}, {"What happened", type text}, {"Root cause (why)", type text}, {"Immediate action", type text}, {"Corrective action (how closed)", type text}, {"Raised by", type text}, {"Owner", type text}, {"Target date", type date}, {"Closed date", type date}, {"Status", type text}, {"Verified by", type text}, {"Source", type text}}),
    Ref = List.Max(Typed[Reported]),
    A1 = Table.AddColumn(Typed, "IsClosed", each if [Status] = "Closed" then 1 else 0, Int64.Type),
    A2 = Table.AddColumn(A1, "Days open", each if [Closed date] <> null then Duration.Days([Closed date] - [Reported]) else Duration.Days(Ref - [Reported]), Int64.Type),
    A3 = Table.AddColumn(A2, "Overdue", each if [Status] <> "Closed" and [Target date] <> null and [Target date] < Ref then 1 else 0, Int64.Type),
    A4 = Table.AddColumn(A3, "Closed on time", each if [Closed date] <> null and [Target date] <> null and [Closed date] <= [Target date] then 1 else 0, Int64.Type),
    A5 = Table.AddColumn(A4, "Age bucket", each if [Status] = "Closed" then "Closed" else if [Days open] <= 7 then "0-7 days" else if [Days open] <= 14 then "8-14 days" else if [Days open] <= 30 then "15-30 days" else if [Days open] <= 60 then "31-60 days" else "60+ days", type text),
    A6 = Table.AddColumn(A5, "AgeSort", each if [Status] = "Closed" then 9 else if [Days open] <= 7 then 1 else if [Days open] <= 14 then 2 else if [Days open] <= 30 then 3 else if [Days open] <= 60 then 4 else 5, Int64.Type),
    A7 = Table.AddColumn(A6, "Month", each Date.ToText([Reported], "MMM yy"), type text),
    A8 = Table.AddColumn(A7, "MonthSort", each Date.Year([Reported]) * 100 + Date.Month([Reported]), Int64.Type),
    A9 = Table.AddColumn(A8, "SeveritySort", each if [Severity] = "Critical" then 1 else if [Severity] = "High" then 2 else if [Severity] = "Medium" then 3 else 4, Int64.Type),
    A10 = Table.NestedJoin(A9, {"Owner"}, HEMAYA_People, {"Name"}, "OP", JoinKind.LeftOuter),
    A11 = Table.ExpandTableColumn(A10, "OP", {"Department"}, {"Owner dept"}),
    A12 = Table.NestedJoin(A11, {"Raised by"}, HEMAYA_People, {"Name"}, "RP", JoinKind.LeftOuter),
    A13 = Table.ExpandTableColumn(A12, "RP", {"Department"}, {"Raised dept"}),
    A14 = Table.ReplaceValue(A13, null, "Other", Replacer.ReplaceValue, {"Owner dept", "Raised dept"})
in
    A14

Stock health / HealthSort

Out of stock · Below minimum · Reorder now · Under repair · On order · Healthy، مع ترتيب.

Stock value SAR

الكمية × سعر الوحدة.

Days since movement

من آخر حركة إلى اليوم.

Days to expected

من اليوم إلى تاريخ الرجوع المتوقع (سالب = تأخر).

Return overdue

1 إذا في الإصلاح أو الطلب وتاريخه فات.

Stock % of max

الكمية ÷ الحد الأعلى.

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblSpares_Register", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Part No", "Description", "Equipment", "Area", "Category", "Criticality", "Location", "Qty on hand", "Min", "Max", "Reorder point", "Unit cost SAR", "Lead time days", "Status", "Repair / order ref", "Sent / ordered", "Expected date", "Last movement", "Note", "Source"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Part No") <> null and Record.Field(_, "Part No") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Part No", type text}, {"Description", type text}, {"Equipment", type text}, {"Area", type text}, {"Category", type text}, {"Criticality", type text}, {"Location", type text}, {"Qty on hand", type number}, {"Min", type number}, {"Max", type number}, {"Reorder point", type number}, {"Unit cost SAR", type number}, {"Lead time days", type number}, {"Status", type text}, {"Repair / order ref", type text}, {"Sent / ordered", type date}, {"Expected date", type date}, {"Last movement", type date}, {"Note", type text}, {"Source", type text}}),
    Ref = Date.From(DateTime.LocalNow()),
    B1 = Table.AddColumn(Typed, "Stock health", each if [Status] = "Under repair (CWS)" then "Under repair" else if [Status] = "On order" and [Qty on hand] <= [Reorder point] then "On order" else if [Qty on hand] = 0 then "Out of stock" else if [Qty on hand] < [Min] then "Below minimum" else if [Qty on hand] <= [Reorder point] then "Reorder now" else "Healthy", type text),
    B2 = Table.AddColumn(B1, "HealthSort", each if [Stock health] = "Out of stock" then 1 else if [Stock health] = "Below minimum" then 2 else if [Stock health] = "Reorder now" then 3 else if [Stock health] = "Under repair" then 4 else if [Stock health] = "On order" then 5 else 6, Int64.Type),
    B3 = Table.AddColumn(B2, "Stock value SAR", each [Qty on hand] * [Unit cost SAR], type number),
    B4 = Table.AddColumn(B3, "Days since movement", each if [Last movement] = null then null else Duration.Days(Ref - [Last movement]), Int64.Type),
    B5 = Table.AddColumn(B4, "Days to expected", each if [Expected date] = null then null else Duration.Days([Expected date] - Ref), Int64.Type),
    B6 = Table.AddColumn(B5, "Return overdue", each if ([Status] = "Under repair (CWS)" or [Status] = "On order") and [Expected date] <> null and [Expected date] < Ref then 1 else 0, Int64.Type),
    B7 = Table.AddColumn(B6, "Stock % of max", each if [Max] = null or [Max] = 0 then null else [Qty on hand] / [Max], type number)
in
    B7

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblSafety_Daily", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "Unsafe conditions", "Unsafe acts", "HEMAYA assigned", "HEMAYA closed", "Incidents", "Major event / note"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"Unsafe conditions", type number}, {"Unsafe acts", type number}, {"HEMAYA assigned", type number}, {"HEMAYA closed", type number}, {"Incidents", type number}, {"Major event / note", type text}})
in
    Typed

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblLLF_Log", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "Equipment", "Finding", "Raised by", "Status", "Closed date"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"Equipment", type text}, {"Finding", type text}, {"Raised by", type text}, {"Status", type text}, {"Closed date", type date}})
in
    Typed

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblProduction_Daily", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "Target MT", "Actual MT", "Downtime hrs", "Major maintenance", "Note"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"Target MT", type number}, {"Actual MT", type number}, {"Downtime hrs", type number}, {"Major maintenance", type text}, {"Note", type text}})
in
    Typed

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblActivities", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "When", "Activity", "Work order", "Crew", "Status", "Note"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"When", type text}, {"Activity", type text}, {"Work order", type text}, {"Crew", type number}, {"Status", type text}, {"Note", type text}})
in
    Typed

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblConcerns", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "Concern / highlight", "Owner", "Due", "Status", "Note"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"Concern / highlight", type text}, {"Owner", type text}, {"Due", type date}, {"Status", type text}, {"Note", type text}})
in
    Typed

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\05_Daily_Meeting\\1_Working\\Daily_Inputs.xlsx"), null, true),
    T = Source{[Item="tblCM_Alerts", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "Area", "Asset", "Parameter", "Level", "Value", "Action", "Status"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "Date") <> null and Record.Field(_, "Date") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"Area", type text}, {"Asset", type text}, {"Parameter", type text}, {"Level", type text}, {"Value", type number}, {"Action", type text}, {"Status", type text}})
in
    Typed

C1 / C2

Area الفاضية أو 0 تصير (No area) عشان ما تختفي من التقارير.

Presence / PresenceSort

حالة مفهومة للعرض: On site، Absent، Sick leave، Permission، Vacation، Day off.

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\02_Attendance\\1_Working\\PAP_Attendance.xlsx"), null, true),
    T = Source{[Item="tblAttendance", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"Date", "EmployeeID", "EmployeeName", "Area", "Craft", "Level", "DefaultShift", "Status", "Shift", "On Site"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "EmployeeID") <> null and Record.Field(_, "EmployeeID") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"Date", type date}, {"EmployeeID", type text}, {"EmployeeName", type text}, {"Area", type text}, {"Craft", type text}, {"Level", type text}, {"DefaultShift", type text}, {"Status", type text}, {"Shift", type text}, {"On Site", type number}}),
    C1 = Table.ReplaceValue(Typed, "0", "(No area)", Replacer.ReplaceValue, {"Area"}),
    C2 = Table.ReplaceValue(C1, null, "(No area)", Replacer.ReplaceValue, {"Area"}),
    C3 = Table.AddColumn(C2, "Presence", each if [On Site] = 1 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 "Other", type text),
    C4 = Table.AddColumn(C3, "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 7, Int64.Type)
in
    C4

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\02_Attendance\\1_Working\\PAP_Attendance.xlsx"), null, true),
    T = Source{[Item="tblEmployees", Kind="Table"]}[Data],
    Kept = Table.SelectColumns(T, {"EmployeeID", "EmployeeName", "Craft", "Level", "Area", "DefaultShift", "ContractType", "EmploymentStatus"}),
    NoBlank = Table.SelectRows(Kept, each Record.Field(_, "EmployeeID") <> null and Record.Field(_, "EmployeeID") <> ""),
    Typed = Table.TransformColumnTypes(NoBlank, {{"EmployeeID", type text}, {"EmployeeName", type text}, {"Craft", type text}, {"Level", type text}, {"Area", type text}, {"DefaultShift", type text}, {"ContractType", type text}, {"EmploymentStatus", type text}})
in
    Typed

Month / MonthSort

شهر الإنجاز الفعلي، والمفتوح يصير Open.

Train

من Area: Area A ← Train A، والباقي Common / Utilities.

Open age / OpenAgeSort

عمر الأمر المفتوح: 0-7، 8-30، 31-90، 91-180، 180+ يوم.

Crew

SDMECHPAP ← Shutdown crew، والباقي Routine crew.

let
    Source = Csv.Document(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\01_WorkOrders\\1_Model\\Fact_WorkOrders.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Kept = Table.SelectColumns(Headers, {"WorkOrderKey", "AssetKey", "WorkCenterKey", "StatusKey", "PriorityKey", "TypeKey", "Area", "WODescription", "ActComp", "PlannedMH", "ActualMH", "OnTime_N", "Open_N", "Done_N", "Emergency_N", "PM_N", "AgeDaysOpen"}),
    Typed = Table.TransformColumnTypes(Kept, {{"WorkOrderKey", type text}, {"AssetKey", type text}, {"WorkCenterKey", type text}, {"StatusKey", type text}, {"PriorityKey", type text}, {"TypeKey", type text}, {"Area", type text}, {"WODescription", type text}, {"ActComp", type date}, {"PlannedMH", type number}, {"ActualMH", type number}, {"OnTime_N", Int64.Type}, {"Open_N", Int64.Type}, {"Done_N", Int64.Type}, {"Emergency_N", Int64.Type}, {"PM_N", Int64.Type}, {"AgeDaysOpen", type number}}),
    D1 = Table.AddColumn(Typed, "Month", each if [ActComp] = null then "Open" else Date.ToText([ActComp], "MMM yy"), type text),
    D2 = Table.AddColumn(D1, "MonthSort", each if [ActComp] = null then 999999 else Date.Year([ActComp]) * 100 + Date.Month([ActComp]), Int64.Type),
    D3 = Table.AddColumn(D2, "Train", each if [Area] = null or [Area] = "" then "No area" else if Text.StartsWith([Area], "Area A") then "Train A" else if Text.StartsWith([Area], "Area B") then "Train B" else if Text.StartsWith([Area], "Area C") then "Train C" else "Common / Utilities", type text),
    D4 = Table.AddColumn(D3, "Open age", each if [Open_N] <> 1 then "Done" else if [AgeDaysOpen] = null then "Unknown" else if [AgeDaysOpen] <= 7 then "0-7 d" else if [AgeDaysOpen] <= 30 then "8-30 d" else if [AgeDaysOpen] <= 90 then "31-90 d" else if [AgeDaysOpen] <= 180 then "91-180 d" else "180+ d", type text),
    D5 = Table.AddColumn(D4, "OpenAgeSort", each if [Open age] = "0-7 d" then 1 else if [Open age] = "8-30 d" then 2 else if [Open age] = "31-90 d" then 3 else if [Open age] = "91-180 d" then 4 else if [Open age] = "180+ d" then 5 else 9, Int64.Type),
    D6 = Table.AddColumn(D5, "Crew", each if [WorkCenterKey] = "SDMECHPAP" then "Shutdown crew" else "Routine crew", type text)
in
    D6

let
    Source = Csv.Document(File.Contents("C:\\Users\\rawab\\OneDrive\\Desktop\\معادن - روابي\\01_WorkOrders\\1_Model\\Dim_Asset.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Kept = Table.SelectColumns(Headers, {"AssetKey", "AssetDescription", "Criticality"}),
    NoDup = Table.Distinct(Kept, {"AssetKey"})
in
    NoDup

let
    D0 = #date(2026, 1, 1),
    Days = List.Dates(D0, 365, #duration(1, 0, 0, 0)),
    T = Table.FromList(Days, Splitter.SplitByNothing(), {"Date"}),
    T1 = Table.TransformColumnTypes(T, {{"Date", type date}}),
    T2 = Table.AddColumn(T1, "Day", each Date.ToText([Date], "ddd"), type text),
    T3 = Table.AddColumn(T2, "DaySort", each Date.DayOfWeek([Date], Day.Sunday), Int64.Type),
    T4 = Table.AddColumn(T3, "Week start", each Date.StartOfWeek([Date], Day.Sunday), type date)
in
    T4

let
    S = #table(type table [Area = text, AreaSort = Int64.Type], {{"Train A", 1}, {"Train B", 2}, {"Train C", 3}, {"Common Area", 4}, {"Cooling Tower", 5}})
in
    S

let
    S = #table(type table [Period = text, PeriodSort = Int64.Type], {{"Day", 1}, {"Week", 2}, {"Month", 3}})
in
    S

let
    D = List.Distinct(List.Transform(Attendance[Date], each Date.From(_))),
    T = Table.FromList(List.Sort(D), Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(T, {{"Date", type date}})
in
    Typed

let
    D = List.Distinct(List.Transform(Production_Daily[Date], each Date.From(_))),
    T = Table.FromList(List.Sort(D), Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(T, {{"Date", type date}})
in
    Typed

هذا الجدول ما له بيانات. سويه بـ Enter data ← اسمه _Measures ← Load، وبعد ما تضيفين أول مقياس احذفي عموده الفاضي.

let Source = #table({"_"}, {{1}}) in Source

COUNTROWS ( HEMAYA_Log ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Status] <> "Closed" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Status] = "Closed" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Overdue] = 1 ) + 0

DIVIDE ( [H Closed], [H Total] )

DIVIDE ( CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Closed on time] = 1 ), [H Closed] )

CALCULATE ( AVERAGE ( HEMAYA_Log[Days open] ), HEMAYA_Log[Status] = "Closed" )

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Type] = "Incident" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Type] = "Near miss" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Type] = "Unsafe act" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Type] = "Unsafe condition" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Type] = "Good practice" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Status] <> "Closed", HEMAYA_Log[Severity] IN { "Critical", "High" } ) + 0

VAR ref = CALCULATE ( MAX ( HEMAYA_Log[Reported] ), ALL ( HEMAYA_Log ) )
VAR last = CALCULATE ( MAX ( HEMAYA_Log[Reported] ), ALL ( HEMAYA_Log ), HEMAYA_Log[Type] = "Incident" )
RETURN IF ( ISBLANK ( last ), BLANK (), INT ( ref - last ) )

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Owner dept] = "Mechanical" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Owner dept] = "Mechanical", HEMAYA_Log[Status] <> "Closed" ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Owner dept] = "Mechanical", HEMAYA_Log[Overdue] = 1 ) + 0

CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Raised dept] = "Mechanical" ) + 0

VAR ref = CALCULATE ( MAX ( HEMAYA_Log[Reported] ), ALL ( HEMAYA_Log ) )
RETURN CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Raised dept] = "Mechanical", HEMAYA_Log[Reported] >= DATE ( YEAR ( ref ), MONTH ( ref ), 1 ) ) + 0

"Open " & FORMAT ( [H Assigned Mech open], "#,0" ) & " · overdue " & FORMAT ( [H Assigned Mech overdue], "#,0" )

"This month " & FORMAT ( [H Raised Mech month], "#,0" ) & " · count only"

"Closed " & FORMAT ( [H Closed], "#,0" ) & " of " & FORMAT ( [H Total], "#,0" )

"Past target date, still open"

"On time " & FORMAT ( [H On-time %], "0%" )

"Near misses " & FORMAT ( [H Near misses], "#,0" )

IF ( NOT HASONEVALUE ( HEMAYA_Log[ID] ), BLANK (),
VAR s = SELECTEDVALUE ( HEMAYA_Log[Status] )
VAR od = MAX ( HEMAYA_Log[Overdue] )
VAR st = SWITCH ( TRUE (), s = "Closed", "CLOSED", od = 1, "OVERDUE", s = "In progress", "IN PROGRESS", "OPEN" )
VAR bg = SWITCH ( st, "CLOSED", "#E3F1E8", "OVERDUE", "#FBE7E5", "IN PROGRESS", "#FBF0D9", "#F1EACB" )
VAR fg = SWITCH ( st, "CLOSED", "#007A3D", "OVERDUE", "#8A340F", "IN PROGRESS", "#A35009", "#343631" )
VAR ic = SWITCH ( st, "CLOSED", "<path d='M8 12 L11 15 L16 9' stroke='" & fg & "' stroke-width='2.2' fill='none' stroke-linecap='round' stroke-linejoin='round'/>", "OVERDUE", "<path d='M12 7 L17 16 L7 16 Z' fill='" & fg & "'/>", "IN PROGRESS", "<circle cx='12' cy='12' r='4.5' fill='none' stroke='" & fg & "' stroke-width='2'/>", "<circle cx='12' cy='12' r='3' fill='" & fg & "'/>" )
VAR svg = "<svg xmlns='http://www.w3.org/2000/svg' width='112' height='24' viewBox='0 0 112 24'>" & "<rect x='0.5' y='0.5' width='111' height='23' rx='11.5' fill='" & bg & "' stroke='" & fg & "' stroke-opacity='0.35'/>" & ic & "<text x='24' y='16' font-family='Segoe UI' font-size='10.5' font-weight='600' fill='" & fg & "'>" & st & "</text></svg>"
RETURN "data:image/svg+xml;utf8," & SUBSTITUTE ( svg, "#", "%23" ) )

IF ( NOT HASONEVALUE ( HEMAYA_Log[ID] ), BLANK (),
VAR d = INT ( MAX ( HEMAYA_Log[Days open] ) )
VAR done = MAX ( HEMAYA_Log[IsClosed] )
VAR sg = SWITCH ( TRUE (), d <= 7, 1, d <= 30, 2, d <= 60, 3, 4 )
VAR col = IF ( done = 1, "#BDBAB5", SWITCH ( sg, 1, "#6B634B", 2, "#6B634B", 3, "#C76210", "#8A340F" ) )
VAR off = "#E0DEDB"
VAR c = "<rect y='6' width='20' height='10' rx='2' "
VAR svg = "<svg xmlns='http://www.w3.org/2000/svg' width='136' height='22' viewBox='0 0 136 22'>" & c & "x='0' fill='" & col & "'/>" & c & "x='22' fill='" & IF ( sg >= 2, col, off ) & "'/>" & c & "x='44' fill='" & IF ( sg >= 3, col, off ) & "'/>" & c & "x='66' fill='" & IF ( sg >= 4, col, off ) & "'/>" & "<text x='94' y='16' font-family='Segoe UI' font-size='11' font-weight='600' fill='" & IF ( done = 0 && sg >= 3, IF ( sg = 3, "#A35009", "#8A340F" ), "#343631" ) & "'>" & d & " d</text></svg>"
RETURN "data:image/svg+xml;utf8," & SUBSTITUTE ( svg, "#", "%23" ) )

VAR d = [H Days since incident]
VAR arc = INT ( MIN ( DIVIDE ( d, 365 ), 1 ) * 163 )
VAR svg = "<svg xmlns='http://www.w3.org/2000/svg' width='64' height='64' viewBox='0 0 64 64'>" & "<circle cx='32' cy='32' r='26' fill='none' stroke='#E0DEDB' stroke-width='6'/>" & "<circle cx='32' cy='32' r='26' fill='none' stroke='#B8A567' stroke-width='6' stroke-linecap='round' stroke-dasharray='" & arc & " 164' transform='rotate(-90 32 32)'/>" & "<text x='32' y='37' text-anchor='middle' font-family='Segoe UI' font-size='15' font-weight='700' fill='#343631'>" & d & "</text></svg>"
RETURN IF ( ISBLANK ( d ), BLANK (), "data:image/svg+xml;utf8," & SUBSTITUTE ( svg, "#", "%23" ) )

COUNTROWS ( Spares_Register ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Stock health] = "Out of stock" ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Stock health] IN { "Below minimum", "Reorder now" } ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Criticality] = "A", Spares_Register[Stock health] IN { "Out of stock", "Below minimum", "Reorder now" } ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Status] = "Under repair (CWS)" ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Status] = "On order" ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Return overdue] = 1 ) + 0

DIVIDE ( CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Stock health] = "Healthy" ), [S Items] )

DIVIDE ( SUM ( Spares_Register[Stock value SAR] ), 1000000 ) + 0

CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Days since movement] > 180 ) + 0

SUM ( Spares_Register[Qty on hand] ) + 0

"Criticality A spares out or below minimum"

"Return overdue " & FORMAT ( [S Return overdue], "#,0" )

"Slow moving over 180 days " & FORMAT ( [S Slow moving], "#,0" )

IF ( NOT HASONEVALUE ( Spares_Register[Part No] ), BLANK (),
VAR q = SUM ( Spares_Register[Qty on hand] )
VAR mn = MAX ( Spares_Register[Min] )
VAR rp = MAX ( Spares_Register[Reorder point] )
VAR mx = MAX ( Spares_Register[Max] )
VAR sc = MAX ( MAX ( mx, q ) * 1.15, 1 )
VAR w = 140
VAR xn = INT ( mn / sc * w )
VAR xr = INT ( rp / sc * w )
VAR xm = INT ( mx / sc * w )
VAR xq = INT ( MIN ( q, sc ) / sc * w )
VAR st = SWITCH ( TRUE (), q <= 0, "OUT", q < mn, "LOW", q <= rp, "ROP", q > mx, "EXCESS", "OK" )
VAR col = SWITCH ( st, "OUT", "#8A340F", "LOW", "#8A340F", "ROP", "#C76210", "EXCESS", "#5F5E5B", "#007A3D" )
VAR tx = IF ( st = "ROP", "#A35009", IF ( st = "EXCESS", "#5F5E5B", col ) )
VAR svg = "<svg xmlns='http://www.w3.org/2000/svg' width='196' height='24' viewBox='0 0 196 24'>" & "<rect x='0' y='7' width='" & w & "' height='10' fill='#F7F3E9'/>" & "<rect x='0' y='7' width='" & xn & "' height='10' fill='#FBE7E5'/>" & "<rect x='" & xn & "' y='7' width='" & MAX ( xr - xn, 0 ) & "' height='10' fill='#FBF0D9'/>" & "<rect x='" & xr & "' y='7' width='" & MAX ( xm - xr, 0 ) & "' height='10' fill='#E3F1E8'/>" & "<rect x='0' y='10' width='" & xq & "' height='4' fill='" & col & "'/>" & "<rect x='" & xr & "' y='5' width='1' height='14' fill='#5E6259'/>" & "<path d='M" & xq & " 7 L" & ( xq - 4 ) & " 1 L" & ( xq + 4 ) & " 1 Z' fill='#343631'/>" & IF ( st = "OUT", "<path d='M146 7 L154 15 M154 7 L146 15' stroke='#8A340F' stroke-width='2.2'/>", "" ) & "<text x='" & IF ( st = "OUT", 160, 148 ) & "' y='16' font-family='Segoe UI' font-size='11' font-weight='600' fill='" & tx & "'>" & INT ( q ) & "</text></svg>"
RETURN "data:image/svg+xml;utf8," & SUBSTITUTE ( svg, "#", "%23" ) )

IF ( ISFILTERED ( 'Pick date'[Date] ), MAX ( 'Pick date'[Date] ), CALCULATE ( MAX ( Attendance[Date] ), ALL ( Attendance ) ) )

SELECTEDVALUE ( Period[Period], "Day" )

VAR d = [Sel date]
RETURN SWITCH ( [Sel period], "Week", d - WEEKDAY ( d, 1 ) + 1, "Month", DATE ( YEAR ( d ), MONTH ( d ), 1 ), d )

VAR d = [Sel date]
RETURN SWITCH ( [Sel period], "Week", d - WEEKDAY ( d, 1 ) + 7, "Month", EOMONTH ( d, 0 ), d )

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( COUNTROWS ( Attendance ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( SUM ( Attendance[On Site] ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( CALCULATE ( SUM ( Attendance[On Site] ), Attendance[Shift] = "Day" ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( CALCULATE ( SUM ( Attendance[On Site] ), Attendance[Shift] = "Night" ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( CALCULATE ( COUNTROWS ( Attendance ), Attendance[Presence] IN { "Absent", "Sick leave" } ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( CALCULATE ( COUNTROWS ( Attendance ), Attendance[Presence] IN { "Vacation", "Day off" } ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( DISTINCTCOUNT ( Attendance[EmployeeID] ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

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

VAR s = [Win start]
VAR e = [Win end]
RETURN CALCULATE ( DISTINCTCOUNT ( Attendance[Date] ), Attendance[Date] >= s, Attendance[Date] <= e ) + 0

DIVIDE ( [A On site], [A Records] )

DIVIDE ( [A Absent], [A Records] )

DIVIDE ( [A Records], [A Active headcount] * MAX ( 1, [A Days logged] ) )

DIVIDE ( [A On site], [A Days logged] )

VAR d = MAX ( Attendance[Date] ) RETURN IF ( d >= [Win start] && d <= [Win end], 1, 0 )

VAR d = MAX ( Calendar[Date] ) RETURN IF ( d >= [Win start] && d <= [Win end], SUM ( Attendance[On Site] ) )

VAR d = MAX ( Calendar[Date] )
VAR sd = [Sel date]
RETURN IF ( ( d < [Win start] || d > [Win end] ) && d >= DATE ( YEAR ( sd ), MONTH ( sd ), 1 ) && d <= EOMONTH ( sd, 0 ), SUM ( Attendance[On Site] ) )

[Sel period] & " · " & IF ( [Win start] = [Win end], FORMAT ( [Win start], "ddd dd MMM yyyy" ), FORMAT ( [Win start], "dd MMM" ) & " to " & FORMAT ( [Win end], "dd MMM yyyy" ) )

"Day " & FORMAT ( [A Day shift], "#,0" ) & " · Night " & FORMAT ( [A Night shift], "#,0" )

"Logged " & FORMAT ( [A Records], "#,0" ) & " of " & FORMAT ( [A Active headcount] * MAX ( 1, [A Days logged] ), "#,0" )

FORMAT ( [A Absent], "#,0" ) & " absent or sick"

"Across " & FORMAT ( [A Days logged], "#,0" ) & " day(s)"

COUNTROWS ( WorkOrders ) + 0

DIVIDE ( SUM ( WorkOrders[PM_N] ), [W Total] )

DIVIDE ( SUM ( WorkOrders[Emergency_N] ), [W Total] )

DIVIDE ( SUM ( WorkOrders[OnTime_N] ), SUM ( WorkOrders[Done_N] ) )

DIVIDE ( SUM ( WorkOrders[ActualMH] ) - SUM ( WorkOrders[PlannedMH] ), SUM ( WorkOrders[PlannedMH] ) )

SUM ( WorkOrders[Open_N] ) + 0

CALCULATE ( SUM ( WorkOrders[PlannedMH] ), WorkOrders[Open_N] = 1 ) + 0

SUM ( WorkOrders[Emergency_N] ) + 0

SUM ( WorkOrders[PM_N] ) + 0

[W Total] - [W PM]

0.8

0.85

"Target 80% · gap " & FORMAT ( [W PM %] - 0.8, "+0.0%;-0.0%" )

"Cap 5% · " & FORMAT ( [W Emergencies], "#,0" ) & " orders"

"Target 85% · ±1 day tolerance"

"Allowed ±10%"

FORMAT ( [W Open], "#,0" ) & " open · backlog " & FORMAT ( [W Backlog MH], "#,0" ) & " h"

IF ( ISFILTERED ( 'Meeting date'[Date] ), MAX ( 'Meeting date'[Date] ), CALCULATE ( MAX ( Production_Daily[Date] ), ALL ( Production_Daily ) ) )

VAR d = [D Meeting date]
RETURN CALCULATE ( SUM ( Production_Daily[Actual MT] ), Production_Daily[Date] = d - 1 ) + 0

VAR d = [D Meeting date]
RETURN CALCULATE ( SUM ( Production_Daily[Target MT] ), Production_Daily[Date] = d - 1 ) + 0

DIVIDE ( [D Actual yesterday], [D Target yesterday] )

VAR d = [D Meeting date]
RETURN CALCULATE ( SUM ( Production_Daily[Downtime hrs] ), Production_Daily[Date] = d - 1 ) + 0

VAR d = [D Meeting date]
RETURN CALCULATE ( SUM ( Safety_Daily[Unsafe conditions] ) + SUM ( Safety_Daily[Unsafe acts] ), Safety_Daily[Date] = d ) + 0

VAR d = [D Meeting date] RETURN CALCULATE ( SUM ( Safety_Daily[Incidents] ), Safety_Daily[Date] = d ) + 0

[H Open]

CALCULATE ( COUNTROWS ( Concerns ), Concerns[Status] <> "Closed" ) + 0

VAR d = [D Meeting date]
RETURN CALCULATE ( COUNTROWS ( Concerns ), Concerns[Status] <> "Closed", Concerns[Due] < d ) + 0

CALCULATE ( COUNTROWS ( CM_Alerts ), CM_Alerts[Status] <> "Closed" ) + 0

CALCULATE ( COUNTROWS ( CM_Alerts ), CM_Alerts[Status] <> "Closed", CM_Alerts[Level] = "Danger" ) + 0

CALCULATE ( COUNTROWS ( LLF_Log ), LLF_Log[Status] = "Open" ) + 0

VAR d = MAX ( Activities[Date] ) RETURN IF ( d = [D Meeting date], 1, 0 )

VAR d = MAX ( Activities[Date] ) RETURN IF ( d = [D Meeting date] - 1, 1, 0 )

VAR d = MAX ( 'Meeting date'[Date] )
RETURN CALCULATE ( SUM ( Production_Daily[Actual MT] ), Production_Daily[Date] = d )

VAR d = MAX ( 'Meeting date'[Date] )
RETURN CALCULATE ( SUM ( Production_Daily[Target MT] ), Production_Daily[Date] = d )

VAR d = MAX ( 'Meeting date'[Date] )
RETURN CALCULATE ( SUM ( Production_Daily[Downtime hrs] ), Production_Daily[Date] = d )

"Target " & FORMAT ( [D Target yesterday], "#,0" ) & " MT · " & FORMAT ( [D Production %], "0%" )

FORMAT ( [D Incidents today], "#,0" ) & " incidents today"

FORMAT ( [D Concerns overdue], "#,0" ) & " past due date"

FORMAT ( [D CM danger], "#,0" ) & " at danger level"

"Daily meeting · " & FORMAT ( [D Meeting date], "dddd dd MMMM yyyy" )

"Sample rows marked DUMMY: HEMAYA " & FORMAT ( CALCULATE ( COUNTROWS ( HEMAYA_Log ), HEMAYA_Log[Source] = "DUMMY sample" ), "#,0" ) & " · spares " & FORMAT ( CALCULATE ( COUNTROWS ( Spares_Register ), Spares_Register[Source] = "DUMMY sample" ), "#,0" )