Forum Discussion
Helper ICalculate 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!
Helper IHi Felix,
Yes I still need help. V-Xiaotang took an excellent stab at it and it looks like it is on the right track but not quite right and I could realllllllly use any help you can provide! V had me create a new column:
Test Act (D) =
var _type= 'Desired Result (Excel)'[Calendar Type]
var _start='Desired Result (Excel)'[Actual Start]
var _end=IF(ISBLANK('Desired Result (Excel)'[Actual Finish]),TODAY(),'Desired Result (Excel)'[Actual Finish])
return
IF(ISBLANK('Desired Result (Excel)'[Actual Start]) && ISBLANK('Desired Result (Excel)'[Actual Finish]),0,
SWITCH(TRUE(),
_type="5 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end && 'Date Table'[5 Day (H)]="Workday")),
_type="6 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end && 'Date Table'[6 Day (H)]="Workday")),
_type="7 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end && 'Date Table'[7 Day (H)]="Workday"))))
But the issues I am encountering with it are:
- It needs to look at the [Calendar Type] (5 Day, 6 Day, 7 Day) and the [Holiday ?] (Holidays, No Holidays). For example, if it is a 5 Day, Holidays then it needs to count the 'Date Table'[5 Day (H)]="Workday". If it is 5 Day, No Holidays then it needs to count 'Date Table'[5 Day (NH)]="Workday"
- If the Actual Finish is Blank (or even better, is the Activity Status = In progress because if it is a Start Milestone it will not have an Actual Finish and the Activity Status = Completed) then the Actual (D) should be the duration from the Actual Start through the Data Date (as opposed to Today's Date) based on the Calendar and Holiday type. A good example of this is PROC.200 - Fabrication/Lead Time - Nanawall Support Steel. It is In progress and it actually started on 11/24/21 and its data date is 11/30/21 but it is showing an actual duration of 14 days instead of 3 days.
If you have time to put another set of eyes on this I would really appreciate it!!
Super UserHi 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?
- Taffalaffa
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!
- MFelix
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.
- Taffalaffa
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?