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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
klp97
Helper I
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])
 
 klp97_1-1663190620464.png

 

 

1 ACCEPTED SOLUTION
klp97
Helper I
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:
 
klp97_0-1663359460075.png

 

View solution in original post

3 REPLIES 3
klp97
Helper I
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:
 
klp97_0-1663359460075.png

 

klp97
Helper I
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.

Anonymous
Not applicable

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?

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_ Binbin Yu

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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