Skip to content

measures.md

Latest commit

 

History

History
180 lines (144 loc) · 5.69 KB

measures.md

File metadata and controls

180 lines (144 loc) · 5.69 KB

🧮 DAX Measure Documentation

This document outlines the core formulas used to drive the Retail Project Dashboard.

1. Time Intelligence & Variance

Measure Name Description Formula Logic
Schedule Variance (SUM) Calculates the day difference between Baseline and Actual/Today. Handles row-level iteration using SUMX. Checks if Actual_Finish exists. If yes, compare to Baseline. If no, compare TodayInPKT to Baseline.
Total_Days_Delayed The average delay across selected projects. Average('Project_schedules'[Days_Delayed])
Schedule Variance Actual Similar to above but uses AVERAGEX for specific summary table contexts. AVERAGEX('Project_Summary', ... logic ...)
SelectedStatusDate Captures the currently selected date context. SELECTEDVALUE('DateTable'[Date])

Complete Formula: Schedule Variance (SUM)

Schedule Variance (SUM) = 
SUMX(
    'Project_schedules',
    VAR ActualFinishDate = 'Project_schedules'[Actual_Finish]
    VAR BaselineFinishDate = 'Project_schedules'[Baseline_Finish]
    
    -- Today's date in Pakistan Standard Time (UTC+5)
    VAR TodayInPKT = DATE(YEAR(UTCNOW() + TIME(5, 0, 0)), MONTH(UTCNOW() + TIME(5, 0, 0)), DAY(UTCNOW() + TIME(5, 0, 0)))
    
    RETURN
    IF(
        -- If an Actual Finish date exists, calculate the variance at completion.
        NOT ISBLANK(ActualFinishDate), 
        DATEDIFF(ActualFinishDate, BaselineFinishDate, DAY),
        
        -- Otherwise, calculate the difference between today (in PKT) and the baseline.
        DATEDIFF(TodayInPKT, BaselineFinishDate, DAY)
    )
)

Complete Formula: Schedule Variance Actual

Schedule Variance Actual = 
AVERAGEX(
    'Project_Summary',
    VAR ActualFinishDate = 'Project_Summary'[Actual_Finish]
    VAR BaselineFinishDate = 'Project_Summary'[Baseline_Finish]
    
    -- Create a variable for today's date in Pakistan Standard Time (UTC+5)
    VAR TodayInPKT = DATE(YEAR(UTCNOW() + TIME(5, 0, 0)), MONTH(UTCNOW() + TIME(5, 0, 0)), DAY(UTCNOW() + TIME(5, 0, 0)))
    
    RETURN
    IF(
        -- If an Actual Finish date exists, the task is complete, so the variance is 0.
        NOT ISBLANK(ActualFinishDate), 
        DATEDIFF(ActualFinishDate, BaselineFinishDate, DAY),
        -- Otherwise, calculate the difference between the correct 'today' and the baseline.
        DATEDIFF(TodayInPKT, BaselineFinishDate, DAY)
    )
)

2. Formatting & UX

Measure Name Description Formula Logic
RowHighlightColor Returns a Hex code for background formatting. IF(PhaseType = "Heading", "#D3D3D3", Blank())
FontColor Returns a color name for text formatting. IF(PhaseType = "Heading", "Black", Blank())

Complete Formula: RowHighlightColor

RowHighlightColor = 
VAR _currentType = MAX('Project_schedules'[PhaseType])
RETURN
IF(_currentType = "Heading", "#D3D3D3", Blank()) -- Light gray for the heading row, blank otherwise

Complete Formula: FontColor

FontColor = 
VAR _currentType = MAX('Project_schedules'[PhaseType])
RETURN
IF(
    _currentType = "Heading",
    "Black", -- Black font for heading rows
    BLANK()  -- Returns null for other rows, using the default table color
)

3. Project Status & Pipeline

Measure Name Description Formula Logic
Projects Under Tendering Counts projects currently in the tendering phase. CALCULATE(DISTINCTCOUNT(...), CONTAINSSTRING(Status, "Tendering"))
Projects On Track Complex filter to count projects with positive milestones. Filters for: LOI Issued, NFA Approved, MPC, Award, CB Opened, PO Issued.
Achieved % (Month) Returns 1 (100%) or 0 based on status string matching. Checks for "LOI Issued", "MPC", "Award", etc.

Complete Formula: Projects Under Tendering

Projects Under Tendering = 
CALCULATE(
    DISTINCTCOUNT('Project Timeline'[Description]),
    CONTAINSSTRING('Project Timeline'[Status], "Tendering")
)

Complete Formula: Projects On Track

Projects On Track = 
CALCULATE(
    COUNTROWS('Project Timeline'),
    FILTER(
        'Project Timeline',
        CONTAINSSTRING('Project Timeline'[Status], "LOI Issued") ||
        CONTAINSSTRING('Project Timeline'[Status], "NFA Approved") ||
        CONTAINSSTRING('Project Timeline'[Status], "MPC") ||
        CONTAINSSTRING('Project Timeline'[Status], "Award") ||
        CONTAINSSTRING('Project Timeline'[Status], "CB Opened") ||
        CONTAINSSTRING('Project Timeline'[Status], "PO Issued")
    )
)

Complete Formulas: Monthly Achievements

Achieved % - July = 
VAR CurrentStatus = SELECTEDVALUE('Project Timeline'[Status])
RETURN IF(CurrentStatus <> BLANK(), 0, 1)

Achieved % - August = 
VAR CurrentStatus = SELECTEDVALUE('Project Timeline'[Status])
RETURN IF(
    CONTAINSSTRING(CurrentStatus, "CB Opened") || 
    CONTAINSSTRING(CurrentStatus, "NFA Approved") || 
    CONTAINSSTRING(CurrentStatus, "CB to be opened") || 
    CONTAINSSTRING(CurrentStatus, "LOI Issued") || 
    CONTAINSSTRING(CurrentStatus, "Approved by MPC"), 
    1, 
    0
)

Achieved % - September = 
VAR CurrentStatus = SELECTEDVALUE('Project Timeline'[Status])
RETURN IF(
    CONTAINSSTRING(CurrentStatus, "LOI Issued") || 
    CONTAINSSTRING(CurrentStatus, "MPC") || 
    CONTAINSSTRING(CurrentStatus, "Award") || 
    CONTAINSSTRING(CurrentStatus, "NFA Approved") || 
    CONTAINSSTRING(CurrentStatus, "CB Opened"), 
    1, 
    0
)

4. Additional Measures

Planned Percentages

Planned % - July = 1
Planned % - August = 1
Planned % - September = 1

These measures are set to 1 (100%) to represent the planned target for each month.