Forum Discussion

flex99's avatar
flex99
Helper I
6 years ago
Solved

Measure dependent on date

I have a visual showing the workload of employees per calendar week and it worked quite nice with below formulas. Workload = Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime] PayedWorkTime = SUM...
Need Help
  • flex99's avatar
    flex99
    6 years ago

    Hey v-chuncz-msft, that is exactly what I meanwhile found myself to work perfectly.

    Just for reasons of completeness the result looks like that:

     

    WorkingHours = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);

    CALCULATE(SUM(Employee[WorkingHours];

    FILTER(Employee;Employee[Employee]<>"NewHireName"));

    CALCULATE(SUM(Employee[WorkingHours]))

     

    EmployeeCount = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);

    CALCULATE(DISTINCTCOUNT(Employee[Employee]);

    FILTER(Employee;Employee[Employee]<>"NewHireName"));

    DISTINCTCOUNT(Employee[Employee]))

     

    WorkDayDount = CALCULATE (

    DISTINCTCOUNT(Plan[Date]);   

    FILTER ('Plan';'Plan'[Weekday] <= 5))

     

    PayedWorkTime = Employee[WorkingHours] / Employee[EmployeeCount] * Plan[WorkDayDount]

     

    Thanks everyone for supporting me!

MFelix's avatar
MFelix
Super User
6 years ago

Hi flex99 ,

 

You can try something similar to:

 

 

Workload = 
SWITCH(
SELECTED(Table[DATE]) < DATE(2020; 4 ; 1);
Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_OLD];

Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_NEW])

But be aware that if you want to calculate values overtime you will need to use an aggregator formula as SUMX.

 

Believe that the best optionm would be to make a distintctcount of the users on a given time and divide the workload by that instead of hardcoding the number of employees.