Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mperrot
Frequent Visitor

Calculate number of working days in current month

Hi,

 

I am having a hard time figuring how to calculate the number of working days already passed since the beginning of the month, as of today.  The number should dynamically change everyday as a result.

 

I have tried using a Calendar table to differentiate the type of days, but I am lost in the way to make it work in the end, and I wonder if there isn't just a simple way to do this..

 

Thanks !

 

 

1 ACCEPTED SOLUTION

Hi @mperrot,

 

I forgot to change it.

 

Please see:

count working days =
CALCULATE (
    COUNTROWS ( 'Dim table' ),
    FILTER (
        'Dim table',
        'Dim table'[Date].[Day] <= DAY ( TODAY () )
            && 'Dim table'[Date].[MonthNo] = MONTH ( TODAY () )
            && 'Dim table'[Weekday] <= 5
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @mperrot,

 

Create a calendar table which lists unique continual dates, and add a calculated column to show weekday number for each date.

Weekday = WEEKDAY('Dim table'[Date],2)

1.PNG

 

Then, create a measure to get the number of working days already passed since the beginning of the month, as of today.

count working days =
CALCULATE (
    COUNTROWS ( 'Dim table' ),
    FILTER (
        'Dim table',
        'Dim table'[Date].[Dia] <= DAY ( TODAY () )
            && 'Dim table'[Date].[MonthNo] = MONTH ( TODAY () )
            && 'Dim table'[Weekday] <= 5
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

 @v-yulgu-msft 

This is what  your solution is returning. Could you help me out here?

 

MoeGhanavizi_0-1642085523718.png

 

 

@v-yulgu-msft 

 

I am also unable to enter any of my column names (mine are called dayofmonth and monthofyear) into the formula - what am I doing wrong?

count working days =
CALCULATE (
COUNTROWS ( 'Date table' ),
FILTER (
'Date table',
'Date table'[Date].[Dayofmonth] <= DAY ( TODAY () )
&& 'Date table'[Date].[Monthofyear] = MONTH ( TODAY () )
&& 'Date table'[Weekday] <= 5
)
)

Thanks Yuliana for your help!

 

What are [Dia] and [MonthNo] referring to ? I get a message telling me they cannot be found in the Date Column

Hi @mperrot,

 

I forgot to change it.

 

Please see:

count working days =
CALCULATE (
    COUNTROWS ( 'Dim table' ),
    FILTER (
        'Dim table',
        'Dim table'[Date].[Day] <= DAY ( TODAY () )
            && 'Dim table'[Date].[MonthNo] = MONTH ( TODAY () )
            && 'Dim table'[Weekday] <= 5
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yulgu-msft ,

 

I have that formula in my calendar. However, we report as of the prior day. On the first of the month I need to count total working days as of last month. 

 

Example: Today is October 1 (22 working days). But, we are reporting September (21 working days). 

 

The below does not work for me for total work days in the reporting period month. But it does when counting actual work days past in the month: 

 
working_days =
CALCULATE ( COUNTROWS ( BI_Calendar ),
FILTER ( BI_Calendar,
AND( BI_Calendar[Month] = MONTH( TODAY () -1 ) ,
not WEEKDAY( BI_Calendar[Day of Week]) in {1,7}
) ))
 
NOTE: The sales Table contains a field [CalendatKey] which is a date (links to [CalendarDate] ) but it is not 
Non Contiguous. 
 
 
Thank you and I give KUDOS and mark as solved!  

Hi iam working through this sme problem. I have calculated the number of working days er month. Happy with that.

 

Your line:

'Dim table'[Date].[Day] <= DAY ( TODAY () )

what is the [Date].[Day] as I do not get that option. is it a column in your date table? If so what format?

 

when I select my weekday  (ie the column <=5) I get a returned value that is the same as the total for the month. today is the 15th, so it can not return 22 for April, which is what I am currently getting. But I can not put the dax exactly as you have it as I dont have [day]. Is it just 1 to 30?

Thank you so much for your help ! Have a great day 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors