Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculating Latest Status Before a Selected Date

Hello everyone,

 

I’m currently working with an audit table that tracks the implementation statuses of various solutions across different plants and divisions, along with the timestamps for when each status was set.

 

I have recently implemented a "Before" slicer to allow users to filter the data based on a selected date. In my use case, I want to filter the table by some measure lets say IsMostRecent to display only the latest status change prior to the selected date. This way, the table would show only one row for each combination of solution, plant, and division — the most recent one before the selected date.

 

I add below the screenshot of what I want to achieve -> only the green rows are visible in the table, as these are the latest entries prior to the selected filter date grouped by division, solution and plant.

 

divisionplantsolution_idstatusTimestampisMostRecent
PS8dd5db1f92ba1b9cPlanned19-06-24 10:471
PS8dd5db1f92ba1b9cIn use06-05-24 2:060
PS8dd5db1f92ba1b9cIn use06-05-24 2:060
PS8dd5db1f92ba1b9cNo status06-05-24 2:050
PS8dd5db1f92ba1b9cIn use06-05-24 2:030
PS8dd5db1f92ba1b9cPlanned06-05-24 2:020
PS8dd5db1f92ba1b9cPlanned06-05-24 2:020
PS8dd5db1f92ba1b9cIn use06-02-24 12:410
PS8dd5db1f92ba1b9cPlanned01-07-22 11:540
PS8dd5db1f92ba1b9cPlanned28-03-22 11:520
PS8dd5db1f92ba1b9cNo status11-08-21 10:560
PS8dd5db1f92ba1b9cPlanned11-08-21 10:560
PS8dd5db1f92ba1b9cNo status27-04-21 13:190
ME8dd51232192ba1b9cNo status06-02-24 12:411
ME8dd51232192ba1b9cPlanned01-07-22 11:540
ME8dd51232192ba1b9cNo status28-03-22 11:520

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous , dharmendars007, thank you for your prompt reply!

     

    • Create the measure as shown below:
    IsMostRecentMeasure = 
VAR SelectedDate = MAX('Date'[Date]) 
VAR CurrentPlant = SELECTEDVALUE('AuditTable'[plant])
VAR CurrentSolution = SELECTEDVALUE('AuditTable'[solution_id])
VAR CurrentDivision = SELECTEDVALUE('AuditTable'[division])

VAR LatestTimestamp = 
    CALCULATE(
        MAX('AuditTable'[Timestamp]),
        FILTER(
            ALL('AuditTable'),
            'AuditTable'[Timestamp] < SelectedDate &&
            'AuditTable'[plant] = CurrentPlant &&
            'AuditTable'[solution_id] = CurrentSolution &&
            'AuditTable'[division] = CurrentDivision
        )
    )

RETURN
IF(
    SELECTEDVALUE('AuditTable'[Timestamp] )= LatestTimestamp,
    1,  
    0   
)
    • Then filter the table visual as shown below:

     

    Result for your reference:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • dharmendars007's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Hello Anonymous , 

     

    Could you please share us the sample data and expected result this will help us to visulize the logic you are mentioning..

     

    Thanks, 

    Dharmendar S

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Added below the original post.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous , dharmendars007, thank you for your prompt reply!

     

    • Create the measure as shown below:
    IsMostRecentMeasure = 
VAR SelectedDate = MAX('Date'[Date]) 
VAR CurrentPlant = SELECTEDVALUE('AuditTable'[plant])
VAR CurrentSolution = SELECTEDVALUE('AuditTable'[solution_id])
VAR CurrentDivision = SELECTEDVALUE('AuditTable'[division])

VAR LatestTimestamp = 
    CALCULATE(
        MAX('AuditTable'[Timestamp]),
        FILTER(
            ALL('AuditTable'),
            'AuditTable'[Timestamp] < SelectedDate &&
            'AuditTable'[plant] = CurrentPlant &&
            'AuditTable'[solution_id] = CurrentSolution &&
            'AuditTable'[division] = CurrentDivision
        )
    )

RETURN
IF(
    SELECTEDVALUE('AuditTable'[Timestamp] )= LatestTimestamp,
    1,  
    0   
)
    • Then filter the table visual as shown below:

     

    Result for your reference:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks Anonymous, this works really fine. I have one more querstion, as I have also other visuals such as Gauge chart, and I would like to filter the whole page by this measure, so the Gauge is also affected by that filter and take only latest values? Is that even possible? I am trying to do something like this:

       

      SumOfCoverageForMostRecentStatus = 
CALCULATE(
    SUM('AuditTable'[coverage]),
    FILTER(
        'AuditTable',
        [IsMostRecentStatus] = 1
    )
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous,

         

        Thank you for your feedback.

         

        As far as I know, measure can not be used in page filter, and we suggest you add the measure to any visuals you want to filter.