Power BI – net workdays month to date

Recently I came across the need to calculate something against amount of work days month to date.

If you tried to do something similar, you found out very quickly that there is no Power BI equivalent to the Excel NETWORKDAYS function.

From a quick research, most people solved this problem by creating a calendar table, and running calculations against it – which seemed like the easy way to go about it, but also one that will require maintenance over time.

So I worked out a way to calculate the net work days month to date with 4 measures, taking today’s date, today’s weekday and the weekday of the first day of the current month into account.

Note: currently I’m only removing weekends as my calculation doesn’t require that level of accuracy (nor did I have the time to work that out).

End result:

Measures details:

[Month Weekday Start] = WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
[Day in Current Month] = Format(TODAY(),"d")
[Week Day Today] = WEEKDAY(TODAY())

[Net Workdays Month to Date] = [Day in Current Month]-IF([Month Weekday Start]=1,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2,INT([Day in Current Month]/7)*2+1),IF([Month Weekday Start]=2,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,INT([Day in Current Month]/7)*2),IF([Month Weekday Start]=3,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,IF([Week Day Today]=1,INT([Day in Current Month]/7)*2+2,INT([Day in Current Month]/7)*2)),IF([Month Weekday Start]=4,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,IF([Week Day Today]<3,INT([Day in Current Month]/7)*2+2,INT([Day in Current Month]/7)*2)),IF([Month Weekday Start]=5,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,IF([Week Day Today]<4,INT([Day in Current Month]/7)*2+2,INT([Day in Current Month]/7)*2)),IF([Month Weekday Start]=6,IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,IF([Week Day Today]<5,INT([Day in Current Month]/7)*2+2,INT([Day in Current Month]/7)*2)),IF([Week Day Today]=7,INT([Day in Current Month]/7)*2+1,IF([Week Day Today]<6,INT([Day in Current Month]/7)*2+2,INT([Day in Current Month]/7)*2))))))))

Hope you find this useful.