Forum Discussion
Calculate Duration Between Two Dates Based on Multiple Criteria (Activity Status, Holiday, Calendar)
Hi. I have a database that provides me the following information:
Project # and Name
Activity ID & Name
Data Date
Start Date (Planned)
Finish Date (Planned)
Actual Start
Actual Finish
Baseline Duration
Remaining Duration
Applicable Calendar (If the activity is on a 5 day, 6 day, or 7 day workweek)
Holidays (if the activity is going to be worked over a holiday or not)
Activity Status (Not Started, In Progress, Completed)
I need to be able to find the Planned Duration and the Actual Duration (for in progress or completed activties) based on the calendar it's on, the holiday status and the activty status. I have done it in Excel but I need to accomplish this in PBI because I am acessing the data from a directly from the database and I cannot figure this out! I am assuming if it is possible in Excel it must be possible here. This is the formula I had to use to figure out the actual durations to date based on the aformentioned criteria:
=IF($F2="Not Started",0,IF(AND($D2="5 DAY",$E2="Holidays",$F2="Completed"),(NETWORKDAYS.INTL($I2,$J2,1,$A$2:$A$232)),(IF(AND($D2="5 DAY",$E2="No Holidays",$F2="Completed"),NETWORKDAYS.INTL($I2,$J2,1),IF(AND($D2="5 DAY",$E2="Holidays",$F2="In Progress"),(NETWORKDAYS.INTL($I2,$H2,1,$A$2:$A$232)),(IF(AND($D2="5 DAY",$E2="No Holidays",$F2="In Progress"),NETWORKDAYS.INTL($I2,$H2,1),IF(AND($D2="6 DAY",$E2="Holidays",$F2="Completed"),(NETWORKDAYS.INTL($I2,$J2,1,$A$2:$A$232)),(IF(AND($D2="6 DAY",$E2="No Holidays",$F2="Completed"),NETWORKDAYS.INTL($I2,$J2,1),IF(AND($D2="6 DAY",$E2="Holidays",$F2="In Progress"),(NETWORKDAYS.INTL($I2,$H2,1,$A$2:$A$232)),(IF(AND($D2="6 DAY",$E2="No Holidays",$F2="In Progress"),NETWORKDAYS.INTL($I2,$H2,1),IF(AND($D2="7 DAY",$E2="Holidays",$F2="Completed"),(DATEDIF($I2-1,$J2,"d")-SUMPRODUCT(($A$2:$A$232>=$I2)*($A$2:$A$232<=$J2))),(IF(AND($D2="7 DAY",$E2="No Holidays",$F2="Completed"),DATEDIF($I2-1,$J2,"d"),IF(AND($D2="7 DAY",$E2="Holidays",$F2="In Progress"),(DATEDIF($I2-1,$H2,"d")-SUMPRODUCT(($A$2:$A$232>=$I2)*($A$2:$A$232<=$H2))),IF(AND($D2="7 DAY",$E2="No Holidays",$F2="In Progress"),(DATEDIF($I2-1,$H2,"d"))))))))))))))))))))
Here is the link for the excel file that has the base data and my desired result:
https://www.dropbox.com/s/cpaudsk5cx2cy9i/Durations.xlsb.xlsx?dl=0
Here is the PBI file that has the tables loaded as well as a base date table:
https://www.dropbox.com/s/y9v07atrmgbeuyd/Durations%20Table.pbix?dl=0
Any help would be greatly appreciated!! I REALLY need to figure this out!
mahoneypat Anonymous Greg_Deckler AlexisOlson v-yalanwu-msft Ashish_Mathur Anonymous v-angzheng-msft amitchandak VahidDM PaulDBrown PhilipTreacy smpa01 MFelix parry2k CNENFRNL rsbin
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!
17 Replies
Taffalaffa You have called out an AMAZING COMMUNITY CONTRIBUTOR. I will let someone else look at this, I'm not too familiar with Excel, etc, and it will take me time to understand the Excel formula before I provide a solution. Someone who is a power Excel user/developerr can easily help. Good luck!
parry2k 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!
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.
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!!
Hi 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.
Hi. 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!!!
Hi v-xiaotang and MFelix
I have updated my table / PBI so hopefully it is clearer what I am looking for. V - I am using your custom table currently and have some errors so hopefully this will make my desired result clearer. I think we are close but just not quite there! Here are the new links:
https://www.dropbox.com/s/37pk7k9tq3zok0n/Durations.xlsx?dl=0
https://www.dropbox.com/s/y9v07atrmgbeuyd/Durations%20Table.pbix?dl=0
Thaks you guys for all your help! I really need to get this figured out (hopefully today!!)
- Tiffany
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.
