Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need to create a measure that calculates the next HoursUnderCapacity based on the next date in the Date column.
I was able to accomplish my goal with this example:
I added one to the filterDate in this code:
But it only worked because the dates were exactly one day after the other. (ex. 3/8/20, 3/9/20, 3/10/20, ...)
The dates in my actual data are random amounts apart:
How can I accomplish adding the difference between each date into my "lastHUC" dax measure calculation so that it retrieves the correct HoursUnderCapacity based on the next date?
I am not able to use EARLIER, because I can only use DirectQuery mode. Unless there's some way to use EARLIER with DirectQuery? I haven't been able to find a way to do that, however.
Solved! Go to Solution.
I solved it by adding an index through ROW_NUMBER() called Row, and then using this dax:
MeasureTest =
VAR IndexNum = MIN(Final[Row])
VAR NextIndexNum =
CALCULATE(
MIN(Final[Row]),
FILTER(
ALLSELECTED (Final),
Final[Row] > IndexNum
)
)
RETURN CALCULATE (
SUM(Final[HoursUnderCapacity]),
FILTER(
ALL(Final),
Final[Row] = NextIndexNum
)
)
I solved it by adding an index through ROW_NUMBER() called Row, and then using this dax:
MeasureTest =
VAR IndexNum = MIN(Final[Row])
VAR NextIndexNum =
CALCULATE(
MIN(Final[Row]),
FILTER(
ALLSELECTED (Final),
Final[Row] > IndexNum
)
)
RETURN CALCULATE (
SUM(Final[HoursUnderCapacity]),
FILTER(
ALL(Final),
Final[Row] = NextIndexNum
)
)
Could you post the sample data in a text format (or copy-paste) instead of a screenshot?
@VasTg Yeah!
First example:
Date | HoursUnderCapacity | lastHUC |
3/8/20 | 0 | 0 |
3/9/20 | 0 | 11 |
3/10/20 | 11 | 14 |
3/11/20 | 14 | 0 |
3/12/20 | 0 | 0 |
Actual data:
Date | HoursUnderCapacity | lastHUC (needed) |
2/27/20 | 0.00 | 9.24 |
3/2/20 | 9.24 | 3.82 |
3/4/20 | 3.82 | 9.25 |
3/9/20 | 9.25 | 6.56 |
3/12/20 | 6.56 | 8.17 |
3/17/20 | 8.17 | 0.00 |
4/28/20 | 0.00 | 0.00 |
lastHUC =
var filterDate = LASTDATE(Final[Date])
RETURN CALCULATE(
SUM(Final[HoursUnderCapacity]),
ALL(Final),
Final[Date] = filterDate + 1
)
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |