Sum values if day name is "Monday", "Tuesday"...

[Nicole_]

Hello,

I am trying to develop a measure that calculates available work hours per day, but accounts for part time employees and also public holidays. Full time employees are easy as standard hours per day = 8, or 0 if there is a public holiday on that day.

However I want to be able to have if the day name = Monday, then sum the relevant values for each resource. The main reason it needs to be broken up like this is if a public holiday falls on a day an individual doesnt work, then they dont get reduced hours. I have a standard calendar_lookup which has dates and related info (day name, day number, start of week, month, year etc) as per the below:

My data is set out similar to the below:

Resource	Monday	Tuesday	Wednesday	Thursday	Friday
A	0	8	8	8	0
B	7.5	5	0	7.5	5
C	8	0	8	8	0
All others	8	8	8	8	8

I have tried unpivoting the data but either way I cant seem to get these to talk. Any help would be appreicated!

Thanks in advance!

[v-henryk-mstf]

Hi @Nicole_ ,

According to your description, the public holiday you are referring to is to be excluded, does it come from a separate date list?

Looking forward to your reply.

Best Regards,
Henry

[Nicole_]

Yes, it is in a separate date list that is connected to the calendar lookup and includes date, day name and PH name.