DAX - SUMPRODUCT Help

Hi all,

I have two columns on my Data Model ,one that checks if it's a working date and another with the employees of each given date.
I tried to create a measure to calculate how many hours these employees will produce(8 Hrs work) which works fine on the pivot table for each date but the total is not showing the correct number(due to the fact that the employees number is not the same every day).

I'm guessing that my Dax is missing a filter or something since i multiply column row by column row..

Example below instead of getting 1120 which is the real sum i get another number)



Date Leaves Employees WorkDay Hours

01/10/2021 7 1 56
02/10/2021 7 0 0
03/10/2021 7 0 0
04/10/2021 7 1 56
05/10/2021 7 1 56
06/10/2021 7 1 56
07/10/2021 7 1 56
08/10/2021 7 1 56
09/10/2021 7 0 0
10/10/2021 7 0 0
11/10/2021 7 1 56
12/10/2021 7 1 56
13/10/2021 7 1 56
14/10/2021 7 1 56
15/10/2021 7 1 56
16/10/2021 7 0 0
17/10/2021 7 0 0
18/10/2021 7 1 56
19/10/2021 7 1 56
20/10/2021 7 1 56
21/10/2021 7 1 56
22/10/2021 7 1 56
23/10/2021 1 7 0 0
24/10/2021 6 0 0
25/10/2021 6 1 48
26/10/2021 6 1 48
27/10/2021 6 1 48
28/10/2021 2 6 1 48
29/10/2021 4 1 32
30/10/2021 4 0 0
31/10/2021 4 0 0
TOTAL 3 7 21 1176




Any suggestion would be more than welcome!

Cheers,
Kyr Manolis

Originally Posted by Exl-Noob

I'm not sure the example file is correct if I parse the info you've provided.
Could you confirm/correct it ?

Hey Exl-Noob,

I re-attached the correct data format.

Cheers

Much easier with the file x)

7 in C34 is(theoretically) the sum of active employees and the 21 in D34 is related with working days per month).

Cheers,
Kyr Manos

The only calculated formula is on my calendar table (to check if it's a working date or not using a SWITCH function) and then i'm trying to multiply these working days with the employees active for each period(and the hours produced for each day)

Measure :
Calculate( COUNTROWS(Table1), Filter (ALLEXCEPT(Table1,Table1[Employee]),Table1[Hire Date] < MAX(Calendar[Date]) && Table1[ExpectedContractEndDate]>=MIN(Calendar[Date])))

Originally Posted by Bo_Ry

Maybe try

=Sumx('Calendar',[Working Days]*[Active_Emps]*8)

Dear Bo_Ry you just made my day!

Thanks to all for the assistance!

Cheers!

What if you need you need to replace the part of multiplying straight with a number and instead using a column value of the contractual hours per employee?


Instead of :
=Sumx('Calendar',[Working Days]*[Active_Emps]*8)

Having Something like :
=Sumx('Calendar',[Working Days]*[Active_Emps]*[Daily_Hours_Of_Emp])

Would i need to create another measure that would just sum my new [Daily_Hours_Of_Emp] calculated column, maybe?