cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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

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)),
0)

#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])

1 ACCEPTED SOLUTION
Helper I

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:

3 REPLIES 3
Helper I

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:

Helper I

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.

Community Support

Hi @klp97 ,

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?

Best regards,
Community Support Team_ Binbin Yu

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors