Calculate difference between 2 dates and show a certain result

[iammrishoabeid]

Hi all,

I am completely new to Power BI and I need your help.

I am trying to write a DAX measure to calculate the difference between 2 dates and display a certain result. There are 3 important dates in use, Today, a CheckDate, and EndDate.

Definitions:

CheckDate - is a scheduled date when an action is due to be completed.

EndDate - is the actual date when the action was completed.

Desired outcome:

I would like to determine the difference between the 2 dates and do the following if these conditions are met:

CHECKS WHEN DELIVERED ON TIME OR LATE

1. IF the END DATE is equal to the CHECK DATE, display "Delivered on time"

2. IF the END DATE is BETWEEN 1 and 2 days AFTER the CHECK DATE, display "Delivered 1-2 days late"

check further for 3-5 days and 5-10 days

3. IF the END DATE is 11 OR MORE days AFTER the CHECK DATE, display "Delivered 11+ days late"

Important note: if there is no End date, check the difference between Today and the Check date and display the "Milestone is running late by X days" where X is the number of days between the 2 dates

CHECKS WHEN DELIVERED EARLIER

4. IF the END DATE is BETWEEN 1 and 2 days BEFORE the CHECK DATE, display "Delivered 1-2 days early"

check further for 3-5 days and 5-10 days

5. IF the END DATE is 11 OR MORE days BEFORE the CHECK DATE, display "Delivered 11+ days early"

CHECKS WHEN DUE TO BE DELIVERED

6. IF TODAY is equal to CHECK DATE, display "Due today"

7. IF TODAY is 1 day TO the CHECK DATE, display "Due in 1 day"

8. IF TODAY is 2 days TO the CHECK DATE, display "Due in 2 days"

check further from 3 days to 29 days

9. IF TODAY is 30 OR MORE days TO the CHECK DATE, display "Due in 30+ days"

The following is the attempt I have made so far and the outcome I have received. It is not the desired outcome that I need.

Milestone Delivery Status =

VAR Today = TODAY()

VAR DaysLate = DATEDIFF('Reporting Table'[End Date], 'Reporting Table'[CheckDate], DAY)

VAR DaysEarly = DATEDIFF('Reporting Table'[CheckDate], 'Reporting Table'[End Date], DAY)

VAR DaysFuture = DATEDIFF('Reporting Table'[CheckDate], Today, DAY)

VAR CheckDateDiff = DATEDIFF('Reporting Table'[CheckDate], Today, DAY)

RETURN

SWITCH(

    TRUE(),

    'Reporting Table'[End Date] = 'Reporting Table'[CheckDate] && 'Reporting Table'[CheckDate] <= Today, "Delivered on time",

    DaysLate >= -2 && DaysLate <= -1, "Delivered 1-2 days late",

    DaysLate >= -5 && DaysLate <= -3, "Delivered 3-5 days late",

    DaysLate >= -10 && DaysLate <= -5, "Delivered 5-10 days late",

    DaysLate >= -11, "Delivered 11+ days late",

    DaysEarly >= -2 && DaysEarly <= -1, "Delivered 1-2 days early",

    DaysEarly >= -5 && DaysEarly <= -3, "Delivered 3-5 days early",

    DaysEarly >= -10 && DaysEarly <= -5, "Delivered 5-10 days early",

    DaysEarly >= -11, "Delivered 11+ days early",

    AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 1 && CheckDateDiff <= 2), "Milestone is running 1-2 days late",

    AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 3 && CheckDateDiff <= 5), "Milestone is running 3-5 days late",

    AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 6 && CheckDateDiff <= 10), "Milestone is running 5-10 days late",

    AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 11), "Milestone is running 11+ days late",

    BLANK()

)

 

[iammrishoabeid]

Hi @dk_dk,

Thanks for looking into this and suggesting a solution.

Unfortunately, when I used the formula, everything defaulted to "Delivered 11+days late" as seen in the image attached. All I did was change the table reference from "Deliveries" to "Reporting Table" which is where all my data resides and the DAX measure is set.

The following is the adjusted formula:

Milestone Delivery Status =

VAR scenario = SWITCH(TRUE(),

MIN('Reporting Table'[End Date])=BLANK(),"NotDelivered",

MIN('Reporting Table'[CheckDate])=MIN('Reporting Table'[End Date]),"DelOnTime",

MIN('Reporting Table'[CheckDate])>MIN('Reporting Table'[End Date]),"DelEarly",

MIN('Reporting Table'[CheckDate])<MIN('Reporting Table'[End Date]),"DelLate",

"Some Error Case")

VAR today = TODAY()

// Check 1

VAR DelOnTime = "Delivered on Time"

//Check 2-3

VAR lateDelDelay=MIN('Reporting Table'[End Date])-MIN('Reporting Table'[CheckDate])

VAR DelLate = SWITCH(TRUE(),

lateDelDelay<=2, "Delivered 1-2 days late",

lateDelDelay<=5, "Delivered 3-5 days late",

lateDelDelay<=10, "Delivered 6-10 days late",

"Delivered 11+ days late")

//Checks 6-9 + Important note

VAR notDelDiff = today-MIN('Reporting Table'[CheckDate])

VAR notDelLateEarlyText = IF(notDelDiff>0,"Milestone is running late by ","Due in ")

VAR notDelDiffGroup = FORMAT(IF(notDelDiff<=-30,"30+",ABS(notDelDiff)),"General Number")

VAR NotDelivered = IF(notDelDiff=0,"Due today",notDelLateEarlyText & notDelDiffGroup & " days")

//Checks 4-5

VAR earlyDelDiff = ABS(lateDelDelay)

VAR DelEarly = SWITCH(TRUE(),

earlyDelDiff<=2, "Delivered 1-2 days early",

earlyDelDiff<=5, "Delivered 3-5 days early",

earlyDelDiff<=10, "Delivered 6-10 days early",

"Delivered 11+ days early")

RETURN

SWITCH(scenario,

"DelOnTime",DelOnTime,

"DelLate",DelLate,

"DelEarly",DelEarly,

"NotDelivered",NotDelivered,

"Something is wrong")

 

[dk_dk · Accepted solution]

Hi @iammrishoabeid 

Could you try some troubleshooting steps:

Delete the Switch statement below RETURN, and instead try returning some of the variables, starting with "scenario", and see at what point is the measure broken. Check for the variables called DelOnTime, DelLate, DelEarly, etc..., if you get the expected result.

Try to make calculated column instead of measure and see if that works.

If you can provide a snapshot of your source data, that would help me in understanding the possible cause as well.

[iammrishoabeid]

Hi @dk_dk,

Thanks again for your suggestions and for supporting me with this issue. I tried the other alternative you suggested and I can confirm that the measure works perfectly now.

Thanks again.

[iammrishoabeid]

Hi @dk_dk,

Thanks for the suggestion. Unfortunately, it still did not work. I managed to play around with my original formula over the weekend last week and got the desired result. However, the code is too much to achieve something so small. I would like to update it to look like your - less code with the same desired effect.

I have yet to try creating a calculated column, but I will give that a shot this weekend. However, on the matter of sharing a snapshot of my source data, would you like to see the star schema or the table from which the appropriate data is coming from?

My current code is as follows:

Measure =
VAR Today = TODAY()
VAR DaysLate = DATEDIFF('Reporting Table'[End Date], 'Reporting Table'[CheckDate], DAY)
VAR DaysEarly = DATEDIFF('Reporting Table'[CheckDate], 'Reporting Table'[End Date], DAY)
VAR DaysFuture = DATEDIFF(Today, 'Reporting Table'[CheckDate], DAY)
VAR CheckDateDiff = DATEDIFF('Reporting Table'[CheckDate], Today, DAY)

RETURN
SWITCH(
TRUE(),
'Reporting Table'[End Date] = 'Reporting Table'[CheckDate] && 'Reporting Table'[CheckDate] <= Today, "Delivered on time",
DaysLate >= -2 && DaysLate <= -1, "Delivered 1-2 days late",
DaysLate >= -5 && DaysLate <= -3, "Delivered 3-5 days late",
DaysLate >= -10 && DaysLate <= -6, "Delivered 6-10 days late",
DaysLate <= -11, "Delivered 11+ days late",

DaysEarly >= -2 && DaysEarly <= -1, "Delivered 1-2 days early",
DaysEarly >= -5 && DaysEarly <= -3, "Delivered 3-5 days early",
DaysEarly >= -10 && DaysEarly <= -6, "Delivered 6-10 days early",
DaysEarly <= -11, "Delivered 11+ days early",

DaysFuture == 0, "Due today",
DaysFuture == 1, "Due in 1 day",
DaysFuture == 2, "Due in 2 days",
DaysFuture == 3, "Due in 3 days",
DaysFuture == 4, "Due in 4 days",
DaysFuture == 5, "Due in 5 days",
DaysFuture == 6, "Due in 6 days",
DaysFuture == 7, "Due in 7 days",
DaysFuture == 8, "Due in 8 days",
DaysFuture == 9, "Due in 9 days",
DaysFuture == 10, "Due in 10 days",
DaysFuture == 11, "Due in 11 days",
DaysFuture == 12, "Due in 12 days",
DaysFuture == 13, "Due in 13 days",
DaysFuture == 14, "Due in 14 days",
DaysFuture == 15, "Due in 15 days",
DaysFuture == 16, "Due in 16 days",
DaysFuture == 17, "Due in 17 days",
DaysFuture == 18, "Due in 18 days",
DaysFuture == 19, "Due in 19 days",
DaysFuture == 20, "Due in 20 days",
DaysFuture == 21, "Due in 21 days",
DaysFuture == 22, "Due in 22 days",
DaysFuture == 23, "Due in 23 days",
DaysFuture == 24, "Due in 24 days",
DaysFuture == 25, "Due in 25 days",
DaysFuture == 26, "Due in 26 days",
DaysFuture == 27, "Due in 27 days",
DaysFuture == 28, "Due in 28 days",
DaysFuture == 29, "Due in 29 days",
DaysFuture >= 30, "Due in 30+ days",

AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 1 && CheckDateDiff <= 2), "Milestone is running 1-2 days late",
AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 3 && CheckDateDiff <= 5), "Milestone is running 3-5 days late",
AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 6 && CheckDateDiff <= 10), "Milestone is running 6-10 days late",
AND(ISBLANK('Reporting Table'[End Date]), CheckDateDiff >= 11), "Milestone is running 11+ days late",

BLANK()
)

[dk_dk]

Hi @iammrishoabeid 

Please try the following formula:

Output = 
VAR scenario = SWITCH(TRUE(),
MIN(Deliveries[End Date])=BLANK(),"NotDelivered",
MIN(Deliveries[Check Date])=MIN(Deliveries[End Date]),"DelOnTime",
MIN(Deliveries[Check Date])>MIN(Deliveries[End Date]),"DelEarly",
MIN(Deliveries[Check Date])<MIN(Deliveries[End Date]),"DelLate",
"Some Error Case")

VAR today = TODAY()

// Check 1
VAR DelOnTime = "Delivered on Time"

//Check 2-3
VAR lateDelDelay=MIN(Deliveries[End Date])-MIN(Deliveries[Check Date])
VAR DelLate = SWITCH(TRUE(),
lateDelDelay<=2, "Delivered 1-2 days late",
lateDelDelay<=5, "Delivered 3-5 days late",
lateDelDelay<=10, "Delivered 6-10 days late",
"Delivered 11+ days late")

//Checks 6-9 + Important note
VAR notDelDiff = today-MIN(Deliveries[Check Date])
VAR notDelLateEarlyText = IF(notDelDiff>0,"Milestone is running late by ","Due in ")
VAR notDelDiffGroup = FORMAT(IF(notDelDiff<=-30,"30+",ABS(notDelDiff)),"General Number")
VAR NotDelivered = IF(notDelDiff=0,"Due today",notDelLateEarlyText & notDelDiffGroup & " days")

//Checks 4-5
VAR earlyDelDiff = ABS(lateDelDelay)
VAR DelEarly = SWITCH(TRUE(),
earlyDelDiff<=2, "Delivered 1-2 days early",
earlyDelDiff<=5, "Delivered 3-5 days early",
earlyDelDiff<=10, "Delivered 6-10 days early",
"Delivered 11+ days early")


RETURN
SWITCH(scenario,
"DelOnTime",DelOnTime,
"DelLate",DelLate,
"DelEarly",DelEarly,
"NotDelivered",NotDelivered,
"Something is wrong")

If you add it as a measure it will work as it is, but you could also add it as a calculated column, in which case you can remove the MIN from everywhere in the formula.

I hope this helps. I tried to structure the code so it is understandable and easy to follow, but if you have any questions let me know.

I have tested with some sample data: