Measure dependent on date

[flex99]

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!

[v-chuncz-msft · Accepted solution]

@flex99 

Just specify an aggregation such as max to get a single result.

https://dax.guide/selectedvalue/

[amitchandak]

Can you count distinct of employee place of static value?

[flex99]

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]

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 · Accepted solution]

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!

[v-chuncz-msft · Accepted solution]

@flex99 

Just specify an aggregation such as max to get a single result.

https://dax.guide/selectedvalue/

[MFelix]

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]

Hi @flex99 ,

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

Thanks,

Pragati