Selecting the most recent row of data based on date selection

[MarDen94]

Hi, 

My dataset is set up as follows: 

A table with status changes for tickets where each status change is one row of data with a unique ID, the tickets can be recognized by a caseID. See also below:

The goal for me would be to select a value in the most recent row of data based on a dynamic (singular) date selection. 

So if the selected date would be 01-03-2022 then a value form first row of data would show for this specific CaseID.

I was able to to this with a MAXX statement:

Selected Status = 
    VAR SelectedCalendarDate = SELECTEDVALUE('Calendar'[CalendarDate])
    VAR SelectedCase = SELECTEDVALUE(Case_history[caseid])
    VAR SelectedHistoryDate = 
        MAXX(
            FILTER( ALL(Case_history),
                Case_history[createddate] < SelectedCalendarDate && Case_history[caseid] = SelectedCase ),
                Case_history[createddate])
    VAR SelectedStatus = 
        MAXX(
            FILTER( ALL(Case_history),
                Case_history[createddate] = SelectedHistoryDate && Case_history[caseid] = SelectedCase),
                Case_history[newvalue])
    RETURN SelectedStatus        

The thing is when aggregating this data for a larger number of tickets the data takes very long to load.

Are there things that I can do to speed things up?

NB. A way of calcaulting where the result is only aggregated data is also acceptable.

[tamerj1]

Hi @MarDen94 
Actually filtering ALL ( Table ) is not required but you're doing that twice.

The filter context already contains the table of each CaseID and no need to filter ALL ( Table ) for the current CaseID. Much more simple formula as follows

Selected Status =
VAR SelectedCalendarDate =
    SELECTEDVALUE ( 'Calendar'[CalendarDate] )
VAR TableBefore =
    FILTER ( Case_history, Case_history[createddate] < SelectedCalendarDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, Case_history[createddate] )
VAR SelectedStatus =
    MAXX ( PreviousRecord, Case_history[newvalue] )
RETURN
    SelectedStatus

[colacan]

@MarDen94 Hi Marden94,

In your measure, there is a nested iterator "MAXX - FILTER(ALL()..." which requires lots of time and resources when data is big.

Try not to filter the whole table (FILTER(column1, column2..) or use other table functions- like CALCULATETABLE rather than FILTER. 

For  your measure, by using calculate you can get the same value as below; 

Status2 =    

   VAR SelectedCalendarDate = SELECTEDVALUE('Calendar'[date])   

   VAR SelectedCase = SELECTEDVALUE(Case_history[caseid])   

   VAR SelectedHistoryDate =        

               CALCULATE(max(Case_history[createddate]),

               Case_history[createddate] < SelectedCalendarDate && Case_history[caseid] = SelectedCase )

   VAR SelectedStatus =

               CALCULATE(MAX(Case_history[newvalue]),

               Case_history[createddate] = SelectedHistoryDate && Case_history[caseid] = SelectedCase)

   RETURN SelectedStatus

Hope this helps you. 
- please mark this post as solution if this post helped. appretiate kudo. 

[MarDen94]

Hi, 
thanks for the reply. 

When using this it gives me the correct status on the selected date. But when I try to use the created 'status2' measure to count the number of cases in a specific status on a historical date it shows all the cases which had that status (eg. "New") before the selected date. 

Would there be an easy way to add this calculation to the measure OR create a seprate measure to that end?

Thanks

[amitchandak]

@MarDen94 , Refer if my blog on a similar topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[MarDen94]

Hi, 

Thank you for your suggestion. But I cannot see how I would add a filter based on date to this calculation. 

I really want to be able to select a date and then get the calculation based on that selected date.

Preferably the date selection is taking place from a different calendar table.