Forum Discussion
Calculate difference between 2 dates and show a certain result
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.
Super UserHi 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.
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()
)