Code Generator | Power BI

Conditional Delivery Date Calculation in Power BI DAX

This document details a DAX function for Power BI that computes the date difference related to delivery timestamps, handling null values and weekend adjustments effectively to facilitate accurate delivery timeline analysis.

Prompt

CASE  WHEN DELIVERY_TMSTP IS NULL THEN 
CASE  WHEN (
DATEDIFF("day",[DEL_COMMIT_TS],Date(Today()))
- FLOOR(2 * (DATEPART("week",Date(Today())) - DATEPART("week",[DEL_COMMIT_TS])))
+ (CASE  WHEN (DayOfWeek(Date(Today()))=6) AND (DayOfWeek([DEL_COMMIT_TS])=0) THEN -1 
         WHEN (DayOfWeek(Date(Today()))=6) OR (DayOfWeek([DEL_COMMIT_TS])=0) THEN 
     0 ELSE 1 END))<0 THEN NULL
ELSE 
DATEDIFF("day",[DEL_COMMIT_TS],Date(Today()))
- FLOOR(2 * (DATEPART("week",Date(Today())) - DATEPART("week",[DEL_COMMIT_TS])))
+ (CASE  WHEN (DayOfWeek(Date(Today()))=6) AND (DayOfWeek([DEL_COMMIT_TS])=0) THEN -1 
         WHEN (DayOfWeek(Date(Today()))=6) OR (DayOfWeek([DEL_COMMIT_TS])=0) THEN 
     0 ELSE 1 END)
END
ELSE 
CASE  WHEN (
DATEDIFF("day",[DEL_COMMIT_TS],[LAST_DAY_AT_LOC_TMSTP])
- FLOOR(2 * (DATEPART("week",[LAST_DAY_AT_LOC_TMSTP]) - DATEPART("week",[DEL_COMMIT_TS])))
+ (CASE  WHEN (DayOfWeek([LAST_DAY_AT_LOC_TMSTP])=6) AND (DayOfWeek([DEL_COMMIT_TS])=0) THEN -1 
         WHEN (DayOfWeek([LAST_DAY_AT_LOC_TMSTP])=6) OR (DayOfWeek([DEL_COMMIT_TS])=0) THEN 
     0 ELSE 1 END))<0 THEN NULL
END
END

Answer

Power BI DAX Function for Conditional Date Calculation

This document outlines a DAX (Data Analysis Expressions) function designed for use in Power BI. The function evaluates the DELIVERY_TMSTP and computes the date difference based on certain conditions and other date fields.

Function Overview

The function determines a date difference based on the presence of a DELIVERY_TMSTP. The logic contains nested conditions to handle scenarios where delivery timestamps are null and calculates differences accordingly.

DAX Function Code

Measure = 
VAR DeliveryTimestampNull = ISBLANK([DELIVERY_TMSTP])
VAR TodayDate = TODAY()
VAR CommitDate = [DEL_COMMIT_TS]
VAR LastDayAtLocation = [LAST_DAY_AT_LOC_TMSTP]

RETURN
IF(
    DeliveryTimestampNull,
    VAR DaysDiffCommitToday = DATEDIFF(CommitDate, TodayDate, DAY)
    VAR WeeksDiff = FLOOR(2 * (WEEKNUM(TodayDate) - WEEKNUM(CommitDate)))
    VAR WeekendAdjustment = 
        SWITCH(
            TRUE(),
            WEEKDAY(TodayDate) = 7 && WEEKDAY(CommitDate) = 1, -1,
            WEEKDAY(TodayDate) = 7 || WEEKDAY(CommitDate) = 1, 0,
            1
        )
    VAR AdjustedDiff = DaysDiffCommitToday - WeeksDiff + WeekendAdjustment
    
    RETURN IF(AdjustedDiff < 0, BLANK(), AdjustedDiff),
    
    VAR DaysDiffCommitLastDay = DATEDIFF(CommitDate, LastDayAtLocation, DAY)
    VAR WeeksDiffLastDay = FLOOR(2 * (WEEKNUM(LastDayAtLocation) - WEEKNUM(CommitDate)))
    VAR WeekendAdjustmentLastDay = 
        SWITCH(
            TRUE(),
            WEEKDAY(LastDayAtLocation) = 7 && WEEKDAY(CommitDate) = 1, -1,
            WEEKDAY(LastDayAtLocation) = 7 || WEEKDAY(CommitDate) = 1, 0,
            1
        )

    RETURN IF(DaysDiffCommitLastDay - WeeksDiffLastDay + WeekendAdjustmentLastDay < 0, BLANK(), DaysDiffCommitLastDay - WeeksDiffLastDay + WeekendAdjustmentLastDay)
)

Explanation

Key Components

Variables: Variables are defined for clarity:
- DeliveryTimestampNull checks if DELIVERY_TMSTP is blank.
- TodayDate, CommitDate, and LastDayAtLocation are assigned relevant fields for easier readability.
Calculations:
- Nested Ifs: The outer layer checks if DELIVERY_TMSTP is null or not.
- Date Difference Calculations: Calculates the date difference using DATEDIFF based on different criteria (current date vs. DEL_COMMIT_TS or vs. LAST_DAY_AT_LOC_TMSTP).
- Week Adjustments: Adjusts for weekends using the WEEKDAY function.

Conditions

If the computed date difference is negative, it returns BLANK() instead of a numeric value to signify no valid date difference.

Usage Example

To integrate this DAX measure into your Power BI report:

Open your Power BI Desktop.
Navigate to the "Modeling" tab and select "New Measure".
Paste the DAX function provided above into the formula bar.
Name the measure (e.g., Delivery Time Computation).
Use this measure in your visualizations to analyze delivery timelines.

Conclusion

Incorporating this DAX measure into your Power BI reports will help capture complex delivery date calculations while handling null timestamps gracefully. If you're eager to deepen your understanding of DAX and Power BI, consider exploring the courses available on the Enterprise DNA Platform.