Forum Discussion
Calculate Duration Between Two Dates Based on Multiple Criteria (Activity Status, Holiday, Calendar)
Hi Taffalaffa ,
Try the following code for the columns (I added on the Base Data Table):
Planned Dur (D) = SWITCH ( TRUE (), 'Base Data'[Holiday?] = "Holidays", COUNTROWS ( FILTER ( ALL ( 'Date Table' ), 'Date Table'[Date] <= 'Base Data'[Finish] && 'Date Table'[Date] >= 'Base Data'[Start] && VAR Calendar_type = SWITCH ( 'Base Data'[Calendar Type], "5 Day", 'Date Table'[5 Day (H)], "6 Day", 'Date Table'[6 Day (H)], "7 Day", 'Date Table'[7 Day (H)] ) RETURN Calendar_type = "Workday" ) ), 'Base Data'[Holiday?] = "No Holidays", COUNTROWS ( FILTER ( ALL ( 'Date Table' ), 'Date Table'[Date] <= 'Base Data'[Finish] && 'Date Table'[Date] >= 'Base Data'[Start] && VAR Calendar_type = SWITCH ( 'Base Data'[Calendar Type], "5 Day", 'Date Table'[5 Day (NH)], "6 Day", 'Date Table'[6 Day (NH)], "7 Day", 'Date Table'[7 Day (NH)] ) RETURN Calendar_type = "Workday" ) ) ) Act Dur (D) = SWITCH ( TRUE (), 'Base Data'[Activity Status] = "Not Started", 0, 'Base Data'[Holiday?] = "Holidays", COUNTROWS ( FILTER ( ALL ( 'Date Table' ), VAR Status_value = SWITCH ( 'Base Data'[Activity Status], "Completed", 'Base Data'[Actual Finish], "In Progress", 'Base Data'[Data Date] ) RETURN 'Date Table'[Date] <= Status_value && 'Date Table'[Date] >= 'Base Data'[Actual Start] && VAR Calendar_type = SWITCH ( 'Base Data'[Calendar Type], "5 Day", 'Date Table'[5 Day (H)], "6 Day", 'Date Table'[6 Day (H)], "7 Day", 'Date Table'[7 Day (H)] ) RETURN Calendar_type = "Workday" ) ), 'Base Data'[Holiday?] = "No Holidays", COUNTROWS ( FILTER ( ALL ( 'Date Table' ), VAR Status_value = SWITCH ( 'Base Data'[Activity Status], "Completed", 'Base Data'[Actual Finish], "In Progress", 'Base Data'[Data Date] ) RETURN 'Date Table'[Date] <= Status_value && 'Date Table'[Date] >= 'Base Data'[Actual Start] && VAR Calendar_type = SWITCH ( 'Base Data'[Calendar Type], "5 Day", 'Date Table'[5 Day (NH)], "6 Day", 'Date Table'[6 Day (NH)], "7 Day", 'Date Table'[7 Day (NH)] ) RETURN Calendar_type = "Workday" ) ) ) At Complete Duration = SWITCH ( 'Base Data'[Activity Status], "Completed", 'Base Data'[Act Dur (D)], "In Progress", 'Base Data'[Remain Dur (D)] + 'Base Data'[Act Dur (D)], "Not Started", 'Base Data'[Remain Dur (D)] )Has you can see below result is matching the excel file:
PBIX file attach.
Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!
Hi Taffalaffa ,
I'm starting to work on this and I have a calculation question.
This year the Christma and New Year is on a saturday, on your calculation you have a double count because it's not a workday and it's an holliday, the example is in the line below:
In this line the total Planned duration is 89 however the actual non workday is 92 because of the holidays that are on saturdays, how do you want to handle the calculation? Should consider the holidays twice has is in the excel?
Hi Felix,
Thanks for your help. For the holidays that are counted twice I believe I have both days marked as a non work day on my dates table.
If we have an activity that started 12/20 and ended 1/3 and was on a 6 Day (H) calendar then that should show as 9 days duration because the federal observed holiday for Christmas this year is 12/24 and New Years is 12/31 so even though it is on a 6 day calendar neither Friday or Saturday would be work days in this scenario. Does that answer your question?
Once again thank you so much for your help!
Hi Taffalaffa ,
Sorry for the follow up quesiton on the example I gave you this is a 5 day calendar the saturdays are non working day should I double count the holidays? On the Excel it's double counting on the specific example I send out.
Hi Felix,
To simplify (and not make me get out a calendar to count all the days in the example you put!) If we have an activity that started 12/20 and ended 1/3 and was on a:
1. 6 Day (H) calendar then that should show as 9 days duration
2. 5 Day (H) calendar then it should show as 9 days duration
3. 6 Day (NH) then it should show as 13 days duration
4. 5 Day (NH) then it should show as 11 Days duration
5. 7 Day (H) then it should show as 11 Days Duration
6. 7 Day (NH) then it should show as 15 Days Duration
on the Calendars that celebrate Holidays, the federal observed holiday for Christmas this year is 12/24 and New Years is 12/31 so even though it is on 5/6/7 day holiday calendar neither Friday or Saturday would be work days in this scenario (but Sunday still would be on the 7 Day)
I see now that my dates table in excel is wrong because it did not account for the observed holidays dates when the holiday fell on a Saturday. I will update and resend!
Does that answer your question?
Thank you . will check and get back to you.