Dax Calculation for Number of Days excluding weekends and holidays

bimbbb · February 20, 2020, 9:04pm

I have a scenario where number of days between start and end date excluding weekends has to be calculated for each person in the data set.
The calculation is a bit straightforward for those who have only one record or row in the dataset but more complicated when an individual has more than one record in the dataset.

I need help with the total number of days calculation for those with multiple start and end dates in the same month and would prefer the calculation to sum all their records as one even though they appear more than once.

Attached is an illustration of the dataset:

Off_Time.xlsx (16.1 KB)

mickeydjw · February 20, 2020, 9:47pm

You can use the formula here to determine weekdays versus weekends, then go from there:

Answer brilliantly stolen from @BrianJ’s reply on that post

BrianJ · February 20, 2020, 9:50pm

@bimbbb,

Please check out the following thread, where I worked out with another member how to perform this same calculation:

DateDiff to only calculate number of work days - #19 by BrianJ

If you can please provide a bit more info on which end date to use (i.e., always use the latest date? always use the actual if there is one?), I can help you work out the specific DAX measure for your situation.

Brian

hafizsultan · February 20, 2020, 11:07pm

Hi @bimbbb,

By taking the idea from @BrianJ post, I have created model for you as per your requirments. Please find attached pbix file.

Idea is simple:

First created calculated column to count no of working days.

 Noofdays Calculated_column = 

             CALCULATE (
                 SUM ( 'Date'[WorkDay] ),
                 DATESBETWEEN (
                     'Date'[Date],
                     'Main_table'[Start Date],
                      IF(ISBLANK('Main_table'[Last Day]),'Main_table'[Estimated Last Day],'Main_table'[Last Day])
                 )
             )

now, after that i created Measure by using below formula:

Total working days =

CALCULATE(SUMX(VALUES('Main_table'),'Main_table'[Noofdays Calculated_column]))

and that’s it and got the result

eDNA_Total working days.pbix (142.6 KB)

BrianJ · February 21, 2020, 2:46am

@hafizsultan,

Nice work. I adapted your solution to subtract workday holiday days.

Step 1: Added Public Holidays table to model (no physical relationship)

Step 2: Added Calc Column to Date Table designating dates that are holidays as 1, otherwise 0

Holiday = 

VAR IsHoliday = 
    LOOKUPVALUE( 
        'Public Holidays'[Holiday Binary], 
        'Public Holidays'[Date], 
        Dates[Date],
        0
    )

RETURN
IF( IsHoliday = 1, 1, 0)

Step 3: Added calculated column to remove workday holidays from total:

Workday Not Holiday = 

SWITCH( TRUE(),
    Dates[DayInWeek] = 5, 0,
    Dates[DayInWeek] = 6, 0,
    Dates[Holiday] =1, 0,
    1
)

Then, adapted your measures to calculate total days using the Workday Not Holiday field from 3) above in place of of your workday field. Here’s the outcome:

Full solution file posted below.

Hope this is helpful.

Brian
eDNA_Total working days not incl holidays solution.pbix (185.9 KB)
.
P.S. I used Australian holidays in my Public Holiday table, because that’s what I had available from the previous example I worked up. However, it’s fairly straightforward to generate these by web scraping from sites like this

using the Power BI web connector.

hafizsultan · February 21, 2020, 3:34am

Hi @BrianJ, Great enhancement to the model and good learning for mr. I hope now solution is 100 % complete

bimbbb · February 21, 2020, 6:40pm

Thanks Brian,

The end date depends on the person’s Off-Time Status. If they are back to work, then we use the actual but if the actual end is blank, it means they are not back, so we use the estimated date.

Abi

BrianJ · February 21, 2020, 6:44pm

@bimbbb,

Perfect – that’s the decision rule that @hafizsultan and I used in our combined effort to provide a solution for you on this. Thanks for the clarification.

Please let us know if what we’ve provided meets your requirements.

Brian

bimbbb · February 21, 2020, 6:51pm

Hi Brian

I’ll work with your solutions and let you know how it goes.

Thanks so much @BrianJ, @hafizsultan, @mickeydjw

bimbbb · February 22, 2020, 4:36am

Hi @hafizsultan

I tested the measures and the number of days are adding up from start date to end date as long as I don’t select a specific month, but I noticed that if the end date is in a different month from the start date and I select a specific month, for example, Jan 2020, the calculation does not count the number of days for the month selected, it counts the total number of days from start date to end date which will result in an incorrect count per month

hafizsultan · February 22, 2020, 4:38am

Hi @bimbbb,

Thanks for the testing. Can you please share snapshot of your issue?

Regards,
Hafiz

bimbbb · February 22, 2020, 4:44am

hafizsultan · February 22, 2020, 5:37am

Hi @bimbbb,

Thanks for sharing snapshot. Yes, current logic will not work in this case as we were getting number of working days between start date and end date. I will try to include above mentioned logic and get back to you.

Cheers!

hafizsultan · February 22, 2020, 5:47am

Hi @bimbbb,

Please see if it serves your requirements.

Total Workdays Not Holidays_V2.0 =

SUMX (
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] >= MAX ( 'Main_table'[Start Date] )
            && 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
            && 'Dates'[Date] <= MAX ( 'Dates'[Date] )
    ),
    'Dates'[Workday Not Holiday]
)

Total Workdays_V2.0 = 

SUMX (
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] >= MAX ( 'Main_table'[Start Date] )
            && 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
            && 'Dates'[Date] <= MAX ( 'Dates'[Date] )
    ),
    'Dates'[WorkDay]
)

In case, if you select particular month, it will count only that Month from start. Otherwise, it will count all dates.

eDNA_Total working days not incl holidays solution.pbix (201.9 KB)

Hopefully, it will resolve your issue

EnterpriseDNA · February 24, 2020, 2:11am

Hi @bimbbb , we’ve noticed that no response has been received from you since 22nd of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

bimbbb · February 24, 2020, 3:24am

Thanks @hafizsultan

I’ll test it and get back to you

bimbbb · February 24, 2020, 1:56pm

I have tested the calculation and this is the result, I got. I also wanted to ask if this formula is supposed to give a total calculation or just a row by row total?

Thanks

hafizsultan · February 24, 2020, 8:23pm

Hi @bimbbb,

Thanks for testing. It’s all about context in which you want to use this formula. I have updated formula to get total right.

It will be for individual rows if you have start date & end date in table as these 2 are different dates. However, if you do not have startdate & lastdate in the table, it will be aggregated like below:

So, it all depends how do you want to use this formula and what should be the filter context.

Please check updated pbix file and see if it serves your purpose.

Total Workdays_V2.0 = 

SUMX (
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] >= MIN ( 'Main_table'[Start Date] )
            && 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
            && 'Dates'[Date] <= MAX ( 'Dates'[Date] )
    ),
    'Dates'[WorkDay]
)

eDNA_Total working days not incl holidays solution V2.0.pbix (205.3 KB)

bimbbb · February 24, 2020, 11:17pm

Thanks so much @hafizsultan. I’ll test and let you know

hafizsultan · February 24, 2020, 11:25pm

You are welcome @bimbbb . Also please note that all calculations will work based on start date as we have active relationship with start date.