[DAX] Calculate with filter?

[RemiAnthonise]

I 've got the following issue:

My employees got their working hours per week. It's a table in AX 365, see image:

I sum the hours in my report so I got the working hours per week (see A: totaal werkuren) . Employee A works 40 hours a week and employee B works 36 hours a week. I want to calculate the availabilty where 40 hours a week = 100%. So I divide the sum of working hours (40 or 36) by 40 and multiply it with 100 (so: 100% or 90%) (see B: inzetbaarheid).

IsWorkDay (C) is a datetable with the following DAX: IsWorkDay = SWITCH(WEEKDAY([Date]);1;0;7;0;1). This works fine.

Werkuren per maand (D) is calculated: CALCULATE(SUM('Calendar'[IsWorkDay])) * [Werkuren 8]. Werkuren 8 is a measure with value 8. I also have a measure Werkuren 9 with the value 9. This goes well for someone who works 5 days a week, 8 hours a day, like employee A. Employee B works 4 days a week, 9 hours a day. Of course, for him column C (isWorkDay) will be less then employee A. I'm afraid I need to create a new measure for him, right? In that case, column D will be easy to calculate: new measure IsWorkDay * measure Werkuren 9. But it would be nice to do it all in one measure / column etc because otherwise it's not really easy to put all my data in 1 matrix. That's why I'm thinking about using Calculate combined with Filter but I'm nog sure it's the right way to solve this. 

Maybe you guys have some advice?

[RemiAnthonise]

I'm sorry that I have to kick this topic back up again.

As you can see in below post, the measure by @v-yulgu-msft doesn't work for Alex as I expected the first row in my measure to be true:

CALCULATE ( SUM ( 'Calendar'[Werkdagen 4 dagen] ) ) * [Werkuren 9] >> 19 x 9 = 171 for august.

[v-yulgu-msft]

Hi @RemiAnthonise,

Maybe you could try:

Werkuren per maand =
IF (
    SELECTEDVALUE ( tablename[Employee] ) = "EmployeeA",
    CALCULATE ( SUM ( 'Calendar'[IsWorkDay] ) ) * [Werkuren 8],
    CALCULATE ( SUM ( 'Calendar'[IsWorkDay] ) ) * [Werkuren 9]
)

Regards,

Yuliana Gu

[RemiAnthonise]

Hi @v-yulgu-msft ,

Thank you for your reply. First of all, I think we miss a thing. I have a calculated column which gives me the workdays in a month, that's IsWorkDay 5 days = SWITCH(WEEKDAY([date]); 1;0;7;0;1). In my case, employee B only works 4 days. So I have to add an extra column which gives me the workdays a month when the workweeks lasts 4 days. But I can't find the right way to modify the DAX this way: IsWorkDay 4 days = SWITCH(WEEKDAY([date]); ????

For example, I've tried IsWorkDay 4 days = SWITCH(WEEKDAY([date]); 1;0;7;-1;1) but this doesn't work well for every month. For example: september 2018 gives me 15 days where it should be 16.

After having this calculated column I think we can go further with your formula.

---

Werkuren per maand =
IF (
    SELECTEDVALUE ( tablename[Employee] ) = "EmployeeA",
 IsWorkDay 4 days * [Werkuren 9];
    IsWorkDay 5 days * [Werkuren 8]

)

---

Than it would be something like this.

[RemiAnthonise]

I did the following:

I've created a new column 

Weekend / weekdays = switch(
true();
'Calendar'[Weekday] = "vrijdag"; "0";
'Calendar'[Weekday] = "zaterdag"; "0";
'Calendar'[Weekday] = "zondag"; "0";
'Calendar'[Weekday] = "maandag"; "1";
'Calendar'[Weekday] = "dinsdag"; "1";
'Calendar'[Weekday] = "woensdag"; "1";
'Calendar'[Weekday] = "donderdag"; "1";
blank()
)

---

where 0 is weekend and 1 is workday. After that, I used the formula that was mentioned before. As you can see in the below image, the total is correct. The lines in my table give me wrong data. How can I solve this?