Working Days For Full Month But Don't Have Full Days In Month

[villa1980]

Hi all,

 I am trying to create a working days measure for the full month within a table (not created by myself) that has a date field but contains the date range from 1st Jan 24 to yesterday (12th Feb 25).

 What I would like to do is show the working day for the full month of Feb (1st - 28th) or any other month even if it does not have the full date range in.

 Do I create a seperate table with the full date range in??

Thanks

Alex 

[ToddChitt · Accepted solution]

Yes, I suggest you have a full Date dimension table. In there you can put in a DAX calculated column to specify that if the week day is Monday through Friday then it is a working day, otherwise not. Make this column a 1 or 0 integer. Then you can set up a simple Measure of "Month To Date Working Days" = TOTALMTD ( SUM ( [Working Day] ), 'Dates'[Date] ).

If you also have a measure of something like MTD Sales or MTD Production, you can get Average Production Per Day by dividing the two: DIVIDE ( [MTD Production], [MTD Working Days] )

Hope this helps.

[Bibiano_Geraldo · Accepted solution]

Hi @villa1980 ,
Yes, it's best practice to create a separate calendar (date) table that includes the full date range you need. A dedicated date table lets you define attributes (like working day flags, month names, etc.) and easily filter or slice your data by any date component. 

Power BI’s best practices recommend using a calendar table to drive time intelligence calculations, ensuring that your measures (like working days) are consistent across any period even when the fact table has gaps.

So, in short: Yes, create a separate table with the full date range to accurately calculate and display working days for any month.

[villa1980]

Thank-you all for your responses, has really helped with my query and worked very well.

[Fowmy · Accepted solution]

@villa1980 

It is always the best practice to create your dates table with full year dates. However, if you need to get the Working days even if the month is not complete, use this measure on a table visual

Working Days =
VAR __CurrentDate =
    MAX ( 'dates'[date] )
VAR __FullMonth =
    CALENDAR ( EOMONTH ( __CurrentDate, -1 ) + 1, EOMONTH ( __CurrentDate, 0 ) )
VAR __WorkingDays =
    COUNTROWS ( FILTER ( __FullMonth, NOT WEEKDAY ( [Date], 1 ) IN { 7, 1 } ) )
RETURN
    __WorkingDays

[Bibiano_Geraldo · Accepted solution]

Hi @villa1980 ,
Yes, it's best practice to create a separate calendar (date) table that includes the full date range you need. A dedicated date table lets you define attributes (like working day flags, month names, etc.) and easily filter or slice your data by any date component. 

Power BI’s best practices recommend using a calendar table to drive time intelligence calculations, ensuring that your measures (like working days) are consistent across any period even when the fact table has gaps.

So, in short: Yes, create a separate table with the full date range to accurately calculate and display working days for any month.

[ToddChitt · Accepted solution]

Yes, I suggest you have a full Date dimension table. In there you can put in a DAX calculated column to specify that if the week day is Monday through Friday then it is a working day, otherwise not. Make this column a 1 or 0 integer. Then you can set up a simple Measure of "Month To Date Working Days" = TOTALMTD ( SUM ( [Working Day] ), 'Dates'[Date] ).

If you also have a measure of something like MTD Sales or MTD Production, you can get Average Production Per Day by dividing the two: DIVIDE ( [MTD Production], [MTD Working Days] )

Hope this helps.