Lookup NEXT value in a series

[Domenick]

I'm trying to find a solution for something that is actually really easy to do in Excel with VLOOKUP and should be doable in PowerBI DAX yet I haven't found any simple solution online.

I want to look up the next refresh time in a table based on the current time.

So I have one table "System Data Refresh" with the current time (and most recent refresh time):

Current Time |  Last Refresh Time
7:45               | 5:00

and another "Refresh Schedule" table with one column of values

Refresh Times
5:00
8:00
11:00
14:00
17:00
20:00

I want to create a column in System Data Refresh that looks up the next refresh time from the current time. So if the current time is 7:45, it would return 8:00. If the current time is 21:00 (after the last refresh time), it would return the first value.

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = coalesce(CALCULATE(MIN('Refresh Schedule'[Refresh Times]),FILTER('Refresh Schedule','Refresh Schedule'[Refresh Times]>EARLIER('System Data Refresh'[Current Time]))),min('Refresh Schedule'[Refresh Times]))

Hope this helps.

[AlexisOlson · Accepted solution]

Take the minimum time after the current time. If such a time doesn't exist, then take the earliest refresh time.

Next Refresh =
VAR CurrentTime = MAX ( System[Current Time] )
VAR NextRefresh =
    CALCULATE (
        MIN ( Refresh[Refresh Times] ),
        Refresh[Refresh Times] > CurrentTime
    )
RETURN
    IF (
        ISBLANK ( NextRefresh ),
        MIN ( Refresh[Refresh Times] ),
        NextRefresh
    )

[Domenick]

Omg yes that makes sense! I'll give it a shot. Thank you!