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!

flex99's avatar
flex99
Helper I
6 years ago

I could introduce a "hire date" to make a distinct count based on that. Is that what you mean? Will give it a try and get back here than.

 

MFelix Your formula looks good and I'd like to try it but two things

1. Power BI tells me that SELECTED is invalid - is there something missing or do I do something wrong?

2. I can't use "Table[DATE]" in a measure - how can I get round this?

 

Thanks for your feedback! Highly appreciated.

MFelix's avatar
MFelix
Super User
6 years ago
Hi @flex98

Its SELECETEDVALUE sorry for the error.
And tha Table[Date] should be replaced by the name of your date column on your model. Since I did not know what was that I made a generic name.
  • flex99's avatar
    flex99
    Helper I
    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!