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 Iparry2k thanks for the response. I tagged you because you have had solved so many issues for people and I really need help! To simplify: I cant figure out how to apply multiple filters from one table: 'Desired Result (PBI)' where [Calendar Type] = "5 Day" and [Holidays ?] = "Holidays" and then if those filters apply, comapre the 'Desired Results (PBI) table to the 'Date Table' whereby the : 'Desired Result (PBI)'[Start] >= 'Date Table' and 'Desired Result (PBI)'[Finish] <= 'Date Table' and count the records that meet that critera of the 'Date Table'[5x8 (H)]="Workday".
I feel like it shouldn't be too complicated but I just can't figure it out and it is causing me a ton of heartache!
- MFelix
Super User
Hi Taffalaffa ,
I will check this later today but if you have an answer already please tell me.
I can tell you that the main issue on Power BI is that you don't have the NETWORKDAYS in a formula so you have to use some workarounds but using the table has you have (with the non workday marked) should work.
- Taffalaffa
Hi 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!!
- MFelix
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?