Take MIN value for every date for each employee

[rank32]

Hello,

I have a problem where I have billable hours and available hours by employee and project.  Every record has 8 hours available built into it.  The issue comes into play when a single employee works on multiple projects in a single day. Since there is a value of 8 available hours in each record, when an employee shows up more than once in a single day (under multiple projects) this causes available hours to be inflated (8 * however many times the employee shows up in single day).  I am looking to create a measure to take the min value for each date for each employee.  This way, I can sum the billable hours against a constant 8 hours available and get a correct utilization measure.  I want this to work both at the individual employee level, but also aggregate correctly when looking at the dataset in it's entirety.  I can't seem to attach a workbook as a new user but here is a link to the workbook in google drive.  Any help is greatly appreciated!

[DOLEARY85]

Hi,

you could create a date table and use something like the below to create a measure of the number of hours per day:

CALCULATE(min(SampleData[AvailableHours]),ALLEXCEPT('date table','date table'[Date]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[rank32]

Appreciate the idea. Correct me if I'm wrong, but I don't think this accounts for the individual employee aspect of this.  This will give me a total value of 8 available hours for the entire org each day.

[DOLEARY85]

Apologies, try this measure=

Measure = var min1 =  CALCULATE(min(SampleData[AvailableHours]))

var totalselectedemp = CALCULATE(DISTINCTCOUNT(SampleData[EmployeeID]))

return

min1*totalselectedemp

just replace it with your available hours in the chart, i've tested against a few dates and the whole chart and it looks like it's working:

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍