Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have a data set that brings in each employee's daily hours worked, split into regular and overhead. I have calculations to sum hours worked in a day, week, etc., but am now trying to create a calculation that only sums hours that are regular when exceeding 40 in a given week. I have created a custom column that provides the Start of Week based on the transaction date, which allows for me to build visuals looking at hours on a weekly basis. But in addition to viewing total regular hours, I want to a sum of only those hours over 40 in a given timeframe.
Here is an example of my data set. For the Start of Week 11/5/2023, this employee has 42 RegHrs, in the week of 11/12 has 42 RegHrs again, and in the week of 11/19 has 36. In this example, the calculation should provide 4 hours over 40 regular hours. Is this possible? Thanks in advance for any assistance.
Employee | TransDate | RegHrs | OHHours | StartOfWeek |
2610 | 11/6/2023 | 8.25 | 2.5 | 11/5/2023 |
2610 | 11/7/2023 | 8.5 | 1.5 | 11/5/2023 |
2610 | 11/8/2023 | 8.25 | 1 | 11/5/2023 |
2610 | 11/9/2023 | 9 | 0.5 | 11/5/2023 |
2610 | 11/10/2023 | 8 | 0.5 | 11/5/2023 |
2610 | 11/13/2023 | 8.5 | 1 | 11/12/2023 |
2610 | 11/14/2023 | 8.5 | 0 | 11/12/2023 |
2610 | 11/15/2023 | 7.75 | 1.5 | 11/12/2023 |
2610 | 11/16/2023 | 9 | 0.5 | 11/12/2023 |
2610 | 11/17/2023 | 8.25 | 1 | 11/12/2023 |
2610 | 11/20/2023 | 9 | 1 | 11/19/2023 |
2610 | 11/21/2023 | 9.5 | 0.5 | 11/19/2023 |
2610 | 11/22/2023 | 8.5 | 0.5 | 11/19/2023 |
2610 | 11/23/2023 | 0 | 8 | 11/19/2023 |
2610 | 11/24/2023 | 9 | 1.25 | 11/19/2023 |
Solved! Go to Solution.
@Anonymous
you can try this
Measure =
var tbl=SUMMARIZE('Table','Table'[Employee],'Table'[StartOfWeek],"hour",sum('Table'[RegHrs])-40)
return sumx(FILTER(tbl,[hour]>0),[hour])
Proud to be a Super User!
@Anonymous
you can try this
Measure =
var tbl=SUMMARIZE('Table','Table'[Employee],'Table'[StartOfWeek],"hour",sum('Table'[RegHrs])-40)
return sumx(FILTER(tbl,[hour]>0),[hour])
Proud to be a Super User!
This is perfect, thank you!
you are welcome
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |