Latest Enterprise DNA Initiatives


Dax Calculation for Number of Days excluding weekends and holidays

DAX Calculations
#1

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)

#2

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

#3

@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

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
1 Like
#4

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:

  1. 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])
                 )
             )

  2. 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 :slight_smile:

image eDNA_Total working days.pbix (142.6 KB)

2 Likes
#5

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

image

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.

3 Likes
Mildly complex date difference formula
#6

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

#7

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

#8

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

Hi Brian

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

Thanks so much @BrianJ, @hafizsultan, @mickeydjw

#10

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

#11

Hi @bimbbb,

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

Regards,
Hafiz

#12

image
image.png1920Ă—1080 239 KB

#13

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!

#14

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.

image
image.png922Ă—497 25 KB

image
image.png924Ă—480 16.4 KB

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

Hopefully, it will resolve your issue :slight_smile:

1 Like
Calculate the work hours between 2 dates( datetime) excluding weekends - 9 am - 6pm Mon-Fri
#15

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!

#16

Thanks @hafizsultan

I’ll test it and get back to you

#17

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

image
image.png1920Ă—1080 293 KB

#18

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.

image
image.png1040Ă—199 7.19 KB

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:

image

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)

#19

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

#20

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