Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
flex99
Helper I
Helper I

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!

2 ACCEPTED SOLUTIONS

@flex99 

 

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

https://dax.guide/selectedvalue/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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!

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

Can you count distinct of employee place of static value?

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

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.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

 

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

https://dax.guide/selectedvalue/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Pragati11
Super User
Super User

Hi @flex99 ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Users online (9,033)