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(Employee[WorkingHours])/6*Plan[WorkDayCount]

 

Now the problem is that there’s a new employee and they want me to show the reduction in workload that this new guy brings to the department.

Previously: 5 employees with total of 197.5 working hours per week

Now: 6 employees with total of 237.5 working hours per week

 

I therefore need to break the established visual into two conditions – before and after the new hire. So how can I tell Power BI to calculate the Workload somehow like that:

Workload = IF “Date” < 01.04.2020 Then

Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_OLD] Else

Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_NEW]

 

My problem is that I can’t reference the “Date_Column” in a measure and when creating a new column I get a circular dependency error!

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!

7 Replies

  • 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.

     

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi flex99 ,

     

     Can you please share some sample data, so that I can suggest the required DAX?

     

    Thanks,

    Pragati

    • 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.