topic Re: Overtime calculation - counting YTD #workweeks to get average in DAX Commands and Tips

Overtime calculation - counting YTD #workweeks to get average

Anonymous — Thu, 15 Sep 2022 22:45:11 GMT

Hi community,

I'm doing a study of over time hours (Hours worked > 40 per employee). I am trying to figure an average number of overtime hours worked per employee per week, but only for the number of weeks YTD.

I have tried to utilize logic with my measures below, but it's not working as I would like it to.

Please see below screenshot - my desired result is ultimately to have a value of 0 for #WorkweeksDC column for workweeks that don't yet have time logged, so that the grand total of weeks at the bottom is only for workweeks YTD.

I'm sure I am missing something simple - thanks in advance for your assistance!

Karen

Measures:

#WorkweeksDC = DISTINCTCOUNT('DimCal'[WorkWeekEndingDate])

#Workweeks_with_curr_week =

var _currworkweekenddate = 'LaborHours'[currworkweekenddate]

var _firstworkweekenddateof_curr_Year = 'LaborHours'[firstworkweekenddateofyear]

var _maxworkweekenddateof_curr_year = 'LaborHours'[maxworkweekenddateofyear]

RETURN

IF(_currworkweekenddate < _maxworkweekenddateof_curr_year,

CALCULATE(DISTINCTCOUNT('DimCal'[WorkWeekEndingDate]),DATESBETWEEN('DimCal'[Date],_firstworkweekenddateof_curr_Year,_currworkweekenddate)),

#WorkWeeks_with_Logic = IF(today()<ENDOFYEAR('DimCal'[Date]),'Labor Hours'[#Workweeks_with_curr_week],[#WorkweeksDC])

Total Hours Worked = SUM('LaborHours'[Total Hours])

Total OT Hrs =

CALCULATE (

SUMX (

SUMMARIZE (

'LaborHours',

'DimCal'[WorkWeekEndYear],

'DimCal'[WorkWeekMonth],

'DimCal'[WorkWeekEndingDate],

'LaborHours'[EmployeeID],

"Overtime", MAX ( [Total Hours Worked] - 40, 0 )

[Overtime]

)

Av Hrs/Emp (OT) = DIVIDE([Total OT Hrs],'LaborHours'[Num Employees])

Av OT Hrs/Emp/Wk = DIVIDE('LaborHours'[Av Hrs/Emp (OT)],[#WorkweeksDC])

Re: Overtime calculation - counting YTD #workweeks to get average

Anonymous — Thu, 15 Sep 2022 07:08:39 GMT

Hi Anonymous ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

Re: Overtime calculation - counting YTD #workweeks to get average

Anonymous — Thu, 15 Sep 2022 17:28:36 GMT

Hello, I've posted the PBIX here: https://drive.google.com/file/d/1czciFzX48kkRQ4kQ4G2ptwKZq8CCTT7N/view?usp=sharing

It's really unfortunate that there is no way to upload a file inline within this forum.

Re: Overtime calculation - counting YTD #workweeks to get average

Anonymous — Fri, 16 Sep 2022 20:17:57 GMT

Hello again,

I believed I solved this on my own. Posting here to vet my solution.

Measures:

#WorkWeeksDC = DISTINCTCOUNT(DimCal[WorkWeekEndingDate])

currworkweekenddate = CALCULATE(MAX('DimCal'[WorkWeekEndingDate]),FILTER('DimCal','DimCal'[Date]=TODAY()))

#Workweeks = CALCULATE([#WorkWeeksDC],CALCULATETABLE('DimCal',DATESBETWEEN('DimCal'[Date].[Date],MIN('DimCal'[Date]),[currworkweekenddate])))

And now #Workweeks measure yields my desired result at both the year level and the month level: