Forum Discussion
Solved
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 Baseli...
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 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.
Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!