Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate difference between 2 dates and show a certain result

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 importan...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • dk_dk's avatar
    dk_dk
    2 years ago

    Hi Anonymous 

    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.




dk_dk's avatar
dk_dk
Icon for Super User rankSuper User
2 years ago

Hi Anonymous 

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: