CODEWITHSUNDEEP
powerbidaxpowerquery
anky
anky

Reputation: 75080

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

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.

Upvotes: 1

Views: 6712

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

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 )
)

Result

Upvotes: 3

Related Questions