Dax Calculation for Number of Days excluding weekends and holidays

DAX DAX Calculations
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