Calculate Number of Working Days based on a Month and Year Column - DAX

Question

I have a column like below for which I would want to extract the number of working days (excluding just weekends- Saturday and Sunday, holidays needs not be addressed).

As of now I just want:

Required_Column = Total No of Days in that month - No of weekends in that month

Month_Year
01-2018
02-2018
03-2018
...
...
01-2019
02-2019

I am a newbie in Power query and DAX , i tried looking up various methods using DAX however, could not find any relevant lead.

Expected Output:

Month_Year   Required_Column
01-2018      23 (31-8)
02-2018      20 (28-8)
03-2018      22 (31-9)
...          ...
...          ...
01-2019      23 (31-8)
02-2019      20 (28-8)

Appreciate your help on this.

The most common and efficient solution is to use Calendar table, which can be build in a data base or in Power Query. Calendar table can have a flag "Is Workday", and then you simply count them. exceleratorbi.com.au/build-reusable-calendar-table-power-query — RADO
Yes, at the moment Calendar table or a self written function (in Power Query) are the only possibilities — Strawberryshrub
I see. I was hoping there would be some Dax formula or function to do it. Nevertheless, thank you both :) — anky

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-12-09T06:18:28

Although a Calendar table based approach is recommended as in the comment by RADO and Strawberryshrub, it is also possible to do this with DAX calculated column.

In the example below, I'm assuming MonthYear column contains the first day of each month.

WorkingDays = 
VAR Year = YEAR( [MonthYear] )
VAR Month = MONTH( [MonthYear] )
VAR DatesInMonth = GENERATESERIES( [MonthYear], DATE( Year, Month + 1, 1 ) - 1, 1 )
RETURN SUMX(
    DatesInMonth,
    IF( WEEKDAY( [Value] ) IN { 1, 7 }, 0, 1 )
)

Calculate Number of Working Days based on a Month and Year Column - DAX

1 Answers