DAX: Count the number of weekdays (not weekends) TM

[BerlinAlexander]

Hi,

I want to have something similar to "countifs" - I want to count the days in the current month which aren't Saturdays or Sundays.

I work with an online DB and can't create additional columns.

My column 'Date'[Date] lists all dates continously, day by day, including weekends.

I have this, which doesn't work:

JustWorkdaysThisMonth = CALCULATE(

    COUNTROWS(DATESMTD('Date'[Date])),

    FILTER('Date', WEEKDAY('Date'[Date]="Saturday") || WEEKDAY('Date'[Date]="Sunday"))

)

Do you have any advice?

[AilleryO]

Hi,

Could you try something like this :

IsWeekDay= SWITCH (MyCalendar[WeekDayName]; "sunday" ; 0; "saturday";0;1)

Then you can do a sum on this column IsWeekDay.

Hope this helps

[amitchandak · Accepted solution]

@BerlinAlexander , Try like

JustWorkdaysThisMonth = CALCULATE(
COUNTROWS(FILTER('Date', WEEKDAY('Date'[Date],2) <6)),DATESMTD('Date'[Date])
)

or

JustWorkdaysThisMonth = CALCULATE(
COUNTROWS('Date'),DATESMTD('Date'[Date])
FILTER('Date', WEEKDAY('Date'[Date],2) <6)
)

[BerlinAlexander]

Thanks a lot. I have one additional question.

It seems that this is considering the whole month.

If I wanted to have MTD instead (i.e. today is July 15th, so I would like to consider just the time since the month beginning till July 15th), how should I change the code?

[AilleryO]

Hi,

You have the STARTOFMONTH() function that should do the trick.