Skip to main content
cancel
Showing results for 
Search instead 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

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.

v-binbinyu-msft
Community Support
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?

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_ Binbin Yu

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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