DAX logic behind working days per month

[Anonymous]

Hi Experts,

I'm trying replicate the below, i have already went through many discussion topics but couldn't figure out a way to do this. Any Dax help is appreciated.   

I have created a date table and already found what are work days excluding all holidays. The goal is to calculate a KPI for each month by working days.

Thanks

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem with the suggestion of TomMartens ?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output.

Best  Regards,

Cherry

[TomMartens · Accepted solution]

Hey,

using this DAX statement to create a simplified Calendar table:

Calendar = 
var datestart = DATE(2001 , 1 , 1)
var dateend = DATE(2004 , 12 , 31)
return
ADDCOLUMNS(
    CALENDAR(datestart , dateend)
    , "Year" , FORMAT(''[Date] , "YYYY")
    , "Month" , RIGHT("0" & FORMAT(''[Date] , "MM") , 2) & " - " & FORMAT(''[Date] , "MMM")
    , "is Workday" , IF(WEEKDAY(''[Date] , 2) <= 5 , 1 , 0)
)

As you can see the column [is workday] returns 1 if the weekday is from Monday to Friday otherwise 0 (you might consider to use BLANK() instead). The calculation for the calculated column [is workday] might be more complex if one has to consider other holidays (here I'm just considering the weekend), but the concept remains the the same: return 1 if it's a workday otherwise 0 or BLANK().

In addition to this I also create a simple measure like so:

No of Workdays = SUM('Calendar'[is Workday])

This allows to create a table like this, be aware that I'm just considering the weekend, for this reason the values vary:

Hopefully this provides some additional insights how to tackle your challenge.

Regards,

Tom

[Anonymous]

Hey @Anonymous 

The WWW is filled with data about this topic.

A few examples:
https://www.youtube.com/watch?v=9M1V_m-oEzc

https://www.youtube.com/watch?v=GLIoDbOiJgw

https://community.powerbi.com/t5/Desktop/Number-of-working-days/td-p/22842

https://www.sqlbi.com/articles/counting-working-days-in-dax/

etc.

Good Luck!
A