Next Date in Column Calculate DAX measure

[Lyssillic]

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.

[Lyssillic · Accepted 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
        )
    )

[VasTg]

@Lyssillic 

Could you post the sample data in a text format (or copy-paste) instead of a screenshot?

[Lyssillic]

@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
    )