Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
For our organization we have a utilization dashboard that tracks work hours on a day by day basis. Reports get uploaded to a SharePoint every morning and Power Bi pulls them in, power query is used to extract the date from the report and create a column, tagging each row in the report with the date. I then use this date column in a slicer to allow the user the ability to look at a specific date. Each report includes the hours from the 1st of the month to the selected date.
I use this measure to calculate indirect overtime for example, the measure shows data for the latest date when multiple dates in the slicer are selected,
TotalOvertimeIndirect = VAR __latestDate = MAX('DateMan'[Date]) RETURN CALCULATE(CALCULATE(SUM('Hours Data'[OVERTIME Hours]), 'Hours Data'[ JobName (1)] = "Indirect Labor") + 0, DateMan[Date] = __latestDate)
However, since the reports include data up until the selected date the hours get added on top of the previous days hours. I'm wondering if its possible to show the difference between the selected date and the previous date. For example, right now, June 1st shows 10 hours and June 2nd shows 20 hours because of the 10 hours on June 2nd added to June 1st's 10 hours. I would like it to just display 10 hours for June 1st and June 2nd.
Thanks
Solved! Go to Solution.
Hi @Anonymous,
You can add date filed to your visual and try to use below measure formula if it works on your side:
TotalOvertimeIndirect =
VAR currDate =
MAX ( 'DateMan'[Date] )
VAR prevDate =
IF ( DAY ( currDate ) <> 1, currDate - 1, currDate )
VAR RollingToDate =
CALCULATE (
SUM ( 'Hours Data'[OVERTIME Hours] ) + 0,
'Hours Data'[ JobName (1)] = "Indirect Labor",
DateMan[Date] = currDate
)
VAR RollingPrev =
CALCULATE (
SUM ( 'Hours Data'[OVERTIME Hours] ) + 0,
'Hours Data'[ JobName (1)] = "Indirect Labor",
DateMan[Date] = prevDate
)
RETURN
IF ( currDate = prevDate, RollingTodate, RollingToDate - RollingPrev )
If above not help, can you please share some dummy data with a similar data structure to help us clarify your data structure and test to coding formula? How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can add date filed to your visual and try to use below measure formula if it works on your side:
TotalOvertimeIndirect =
VAR currDate =
MAX ( 'DateMan'[Date] )
VAR prevDate =
IF ( DAY ( currDate ) <> 1, currDate - 1, currDate )
VAR RollingToDate =
CALCULATE (
SUM ( 'Hours Data'[OVERTIME Hours] ) + 0,
'Hours Data'[ JobName (1)] = "Indirect Labor",
DateMan[Date] = currDate
)
VAR RollingPrev =
CALCULATE (
SUM ( 'Hours Data'[OVERTIME Hours] ) + 0,
'Hours Data'[ JobName (1)] = "Indirect Labor",
DateMan[Date] = prevDate
)
RETURN
IF ( currDate = prevDate, RollingTodate, RollingToDate - RollingPrev )
If above not help, can you please share some dummy data with a similar data structure to help us clarify your data structure and test to coding formula? How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |