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.
My company needs a scheduling report for the hours worked on machines. We can only use DirectQuery, not import due to security reasons.
What I have so far is a table that has the HoursRemaining (the hours that need to be done that day), the HoursUnderCapacity (the hours that are leftover, are free), and the HoursOverCapacity (the hours that are over the planned hours for that machine on that day). The x-axis is the date.
We would like the HoursOverCapacity to be added on to the next day's over or under capacity. For example, if the previous day had hours over it's capacity, put it onto the remaining hours of the current day. If the current day had hours under capacity, the previous day's hours over capacity would subtract from the free hours that were available that day. And if there was hours over capacity of that day, then it would add the previous day's hours over capacity to the current day's.
I've created this 'TestingColumn,' which is a calculated column with the following DAX:
TestingColumn =
var prevHOC = 'Testing Totals'[prevHOC]
var currentHOC = 'Testing Totals'[HoursOverCapacity]
var currentHUC = 'Testing Totals'[HoursUnderCapacity]
RETURN IF (
prevHOC > 0,
IF (
currentHOC > 0,
prevHOC + currentHOC,
IF (
currentHUC > 0,
ABS(prevHOC - currentHUC),
0
)
),
IF (
currentHOC > 0,
currentHOC,
IF (
currentHUC > 0,
currentHUC,
0
)
)
)
It correctly adds/subtracts the previous day's HoursOverCapacity to the current day's. However, when it goes to the day after to continue adding/subtracting HoursOverCapacity, it doesn't retrieve the updated value from the previous day that was just calculated. We need it to update the values as it goes, subtracting/adding and continuing the logic.
Here's an example of the data:
Date | HoursUnderCapacity | HoursOverCapacity |
3/8/20 | 0 | 22.23 |
3/9/20 | 0 | 16.58 |
3/10/20 | 11.64 | 0 |
3/11/20 | 0 | 18.08 |
We want it to look like:
Date | HoursUnderCapacity | HoursOverCapacity |
3/8/20 | 0 | 22.23 |
3/9/20 | 0 | 38.81 |
3/10/20 | 0 | 27.17 |
3/11/20 | 0 | 45.25 |
So adding 22.23 + 16.58 = 38.81,
Then subtracting 38.81 (over capacity hours) - 11.64 (free hours) = 27.17 (still over capacity),
And finally adding 27.17 + 18.08 = 45.25.
How can I do this within a DAX calculated column? Or a different, more efficient way?
Thanks!
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |