Help with Measure

[almarfivaz]

Hi Community,

I hope you can assist me with the following? Just cannot seem to grasp it...

I need to dynamically add 8 working hours to every day based on the date selection in a slicer.

With this, I am trying to calculate the employee utilization based on an 8-hour working day.

So, one day equals 8 hours, two days 16 hours, etc. then calculate the employee utilization against hours worked from a table where time is captured.

Thank you!

[BA_Pete · Accepted solution]

Hi @almarfivaz ,

I'm assuming the dates in your slicer are from a calendar dimension table, but should work either way.

Try a measure like this:

_workingHours = DISTINCTCOUNT(calendar[Date]) * 8

Pete

[almarfivaz]

Hi Pete,

Thnx for the reply.

Tried the measure and have a value of 75992 for 4 days based on the slicer date (1st to 4th October). I believe the value should be 96 hours for 4 days. Even if I adjust the slicer to 2 weeks the value still remains 75992.

Regards,

[BA_Pete]

@almarfivaz ,

Ok. It's clearly picking up all available dates regardless of the slicer. I think the value we're looking for will be 32 hours for those four days.

Did you use the same date column in your measure that you are using in the slicer?

Works fine when I do it using calendar[date] in both measure and slicer:

Pete

[almarfivaz]

Thank you Pete, you were correct, I was not using the same calendar[date]. Now that I have the slicer together with the sum of the days, as chosen in the slicer, matches.

However, I have run into another issue now 😢. I need to filter on the actual time worked in hours by the employees, table: workload[createddate]. Once I filter on the calendar[date] together with the workload[createddate] I do not see all the data. I am using an SSAS Live connection.

[BA_Pete]

Hi @almarfivaz ,

Very difficult to diagnose the issue with the limited information available.

I would recommend opening a new topic in the Desktop forum providing as much information as possible about your situation, model, and required outcomes.

Pete

[almarfivaz]

Awesome will do Pete, Thnx!

[Anonymous]

Hi @almarfivaz ，

Is there any other part of this case that you need help with? If not, could you please mark the helpful post as Answered? This will help the members in the community who have the same problem as you to find a solution easily. If there are any other new problem, you can open a new case in the community. Thank you.

Best Regards