Counting Working Days with DAX

Alberto Ferrari shows how we can ignore weekends in date calculations with DAX:

How is it possible to compute the difference between the two dates, only computing working days and skipping weekends and holidays? Simple math is no longer useful here, and DAX does not offer a predefined function.

A solution to this scenario requires a date table – more details here – with a specific column, IsWorkingDay, which indicates whether that particular day is a working day or not. The following figure shows an example:

Another good use of date tables (AKA calendar tables), which are also quite useful in T-SQL queries.

Related Posts

Formatting Lists of Values with DAX

Alberto Ferrari and Patrick LeBlanc have a great video on formatting lists of filter values in DAX like 2003, 2005-2007, 2009: Alberto Ferrari joins Patrick to walk through how you can use DAX to format a list of values within Power BI Desktop. This takes the concatenate values quick measure to the next level. Transmuting […]

Read More

Running Totals in DAX

Alberto Ferrari shows how you can calculate running totals in DAX: A very common calculation in DAX is the year-to-date calculation (YTD), which aggregates values from the beginning of the year all the way to a certain date. A simple implementation uses the predefined DATESYTD function: Sales YTD := CALCULATE ( [Sales Amount], DATESYTD( 'Date'[Date] )) […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

March 2019
MTWTFSS
« Feb  
 123
45678910
11121314151617
18192021222324
25262728293031