Forum Discussion

Mattula's avatar
Mattula
Frequent Visitor
2 years ago
Solved

Next Occurrence of value

I have a table of events and I want in a card to retrieve the next event which has the status 'Planned'. 

 

My table looks like this 

 

DateStatusEvent
11/12/2023ConfirmedEvent 1
12/12/2023ConfirmedEvent 2
13/12/2023CancelledEvent 3
14/12/2023ConfirmedEvent 4
15/12/2023PlannedEvent 5
16/12/2023CancelledEvent 6
17/12/2023PlannedEvent 7
18/12/2023NEWEvent 8
19/12/2023NEWEvent 9
20/12/2023PlannedEvent 10

 

I have used this Measure which is retrieving a value but isn't the next occuring event in the date order from today's date 

 

 

Next Event =
CALCULATE(MIN(TABLE1[EVENT]),
FILTER(
    TABLE1,
    TABLE1[EVENT_STATUS] = "Planning" &&
    TABLE1[Date] >= NOW()
)
)
  • vicky_'s avatar
    vicky_
    2 years ago

    Here's the measure I have: 

     

    Next Event = 
var earliestPlannedDate = CALCULATE(MIN('Table'[Date]), 'Table'[Date] >= TODAY() && 'Table'[Status] = "Planned")
return CALCULATE(MIN('Table'[Event]), 
    FILTER(
        'Table',
        'Table'[Date] = earliestPlannedDate
    )
)

     

    Just a quick note - the TODAY() and NOW() functions are based on UTC time, but you should be able to find many threads on here about converting to local time. 

     

    And the reason that your measure didn't work is because the MAX([Event]) will grab the first event alphabetically, so between events 7 and event 10, the "1" would come before "7", resulting in the incorrect result you saw above.

     

2 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    Here's the measure I have: 

     

    Next Event = 
var earliestPlannedDate = CALCULATE(MIN('Table'[Date]), 'Table'[Date] >= TODAY() && 'Table'[Status] = "Planned")
return CALCULATE(MIN('Table'[Event]), 
    FILTER(
        'Table',
        'Table'[Date] = earliestPlannedDate
    )
)

     

    Just a quick note - the TODAY() and NOW() functions are based on UTC time, but you should be able to find many threads on here about converting to local time. 

     

    And the reason that your measure didn't work is because the MAX([Event]) will grab the first event alphabetically, so between events 7 and event 10, the "1" would come before "7", resulting in the incorrect result you saw above.

     

    • Mattula's avatar
      Mattula
      Frequent Visitor
      2 years ago

      This worked perfectly. Thank you