Reply
BerlinAlexander
Helper II
Helper II
Partially syndicated - Outbound

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

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?

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

4 REPLIES 4
AilleryO
Memorable Member
Memorable Member

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Syndicated - Outbound

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?

Syndicated - Outbound

Hi,

 

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

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)