cancel
Showing results 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

Helper I

## Workday calculation

Hi Experts,

I have requirement to calculate workday per month.

The Logic for workday calculation is:
Number of days per month minus public holidays, Saturdays, and Wednesdays. If a public holiday falls on a Wednesday, it should be additionally subtracted.

In the Master calender. I have a sepearet column to identify IsHoliday, IsSaturday and IsWednesday.

Attahced is the PBIX

Workdays.pbix

Thank you!

1 ACCEPTED SOLUTION
Super User

Hi @puru85
Modify the formula of column isworkday to :

Not work day =

IF('MasterCalendar'[Date] IN VALUES('Public Holiday'[Date]) && MasterCalendar[Weekday Number] =4,2,
IF('MasterCalendar'[Date] IN VALUES('Public Holiday'[Date]) ||  MasterCalendar[Weekday Number] =7 ||  MasterCalendar[Weekday Number] =4,1,0
))

Result :

modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
5 REPLIES 5
Helper I

Hi @Ritaf1983 @mickey64 @ryan_mayu ,

Thank you for helping me.
The measure you have created is calculating:
Number of days per month minus public holidays, Saturdays, and Wednesdays.

But requirement is:
Number of days per month minus public holidays, Saturdays, and Wednesdays. If a public holiday falls on a Wednesday, it should be additionally subtracted.

Please refer to below Master Calender Table for May 2024.

1 May and 22 May is Public Holiday which falls on Wednesday so it should be additionally subtracted.

So the number of working days for May 2024 will be: 20.

Super User

Hi @puru85

You can add column to the calender like :

Not work day =
IF('MasterCalendar'[Date] IN VALUES('Public Holiday'[Date]) ||  MasterCalendar[Weekday Number] =7 ||  MasterCalendar[Weekday Number] =4,1,0)
And measure :
Workdays count Rita = COUNTROWS('MasterCalendar')-sum('MasterCalendar'[Not work day])
Result:

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Hi @puru85
Modify the formula of column isworkday to :

Not work day =

IF('MasterCalendar'[Date] IN VALUES('Public Holiday'[Date]) && MasterCalendar[Weekday Number] =4,2,
IF('MasterCalendar'[Date] IN VALUES('Public Holiday'[Date]) ||  MasterCalendar[Weekday Number] =7 ||  MasterCalendar[Weekday Number] =4,1,0
))

Result :

modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Step 0: I use your PBIX file.

Step 1: I make 3 measures below.

M_All Days = COUNTROWS('MasterCalendar')

M_Holiday_Wed_Sat = COUNTROWS(FILTER('MasterCalendar','MasterCalendar'[IsHoliday]=TRUE()||'MasterCalendar'[IsSaturday]=TRUE()||'MasterCalendar'[IsWednesday]=TRUE()))

M_Workdays = [M_All Days]-[M_Holiday_Wed_Sat]

Step 2: I make a matrix below.

Super User

is this what you want?

Measure = COUNTROWS('MasterCalendar')-CALCULATE(COUNTROWS(MasterCalendar),FILTER(MasterCalendar,MasterCalendar[Weekday]="Wednesday"))-CALCULATE(COUNTROWS('Public Holiday'),FILTER('Public Holiday','Public Holiday'[Date]<=min('MasterCalendar'[Date])&&'Public Holiday'[Date]<=max('MasterCalendar'[Date])))

Proud to be a Super User!

Announcements

#### 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.