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!
Community SupportHi Taffalaffa
I create a column,
Test =
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"))))this column calculate the diff between [Actual Finish] & [Actual Finish], according to calendar type, for example,
and this is the result,
not sure if I understand you correctly, if you need more help, please let me know.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Helper IHi. Thank you for the help. It is on the right track but not quite right. A few notes...
1. 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"
2. 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 opposted 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.
Thank you thank you thank you for helping me with this!!!