Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Taffalaffa
Helper I
Helper I

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 @v-yiruan-msft @Greg_Deckler @AlexisOlson @v-yalanwu-msft @Ashish_Mathur @v-rzhou-msft @v-angzheng-msft @amitchandak  @VahidDM @PaulDBrown @PhilipTreacy @smpa01 @MFelix @parry2k @CNENFRNL @rsbin 

2 ACCEPTED SOLUTIONS

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:

MFelix_0-1639520773797.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!

View solution in original post

17 REPLIES 17
parry2k
Super User
Super User

@MFelix My small brain cannot handle that. I will leave that in the hands of experts like you 🙂 but thanks for the offer.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

Great job big guy @MFelix . Awesome job there. Cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you @parry2k ,

 

If you want I can teach you some excel.

 

🤣


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



v-xiaotang
Community Support
Community Support

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,

vxiaotang_0-1639383702048.png

vxiaotang_1-1639383723653.png

and this is the result, 

vxiaotang_2-1639383820277.png

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 @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

 

Taffalaffa_0-1639508594299.png

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:

MFelix_0-1639520773797.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!

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!!!

Taffalaffa
Helper I
Helper I

@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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

  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 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'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:

MFelix_0-1639481916301.png

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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! 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

Thank you . will check and get back to you.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



parry2k
Super User
Super User

@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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.