How to find the most recent date for the previous category in DAX?

0
votes

I am looking to create calculated column/s that will achieve the following goal: I need to calculate the maximum date for a previous episode of care (see data below). I am able to use the earlier() function to get the maximum date for the first and last episode of care, but I need it to be the previous episode rather than just the first or last. I am using calculated columns rather than measures because I need person-level data and need to do further calculations.

This is my desired outcome:

desired outcome

What I am able to achieve using earlier() function:

![wrong answer

When a person has more than two episodes is where my formulas stop working. My formulas are similar to this:

earliest date = calculate(min([date]), filter(table, [person] = earlier([person]))

I've looked on the internet and similar questions but haven't found an example that worked. Please help!

1
powerbidax

1 Answers

1
votes

You need to filter for previous episode too.

MaxDatePrevEpisode =
CALCULATE (
    MAX ( Table1[date] ),
    ALLEXCEPT ( Table1, Table1[Person] ),
    Table1[episode] < EARLIER ( Table1[episode] )
)

This finds that last date in the table where the person matches and the episode is strictly less than the one for the current row. ALLEXCEPT removes any filtering on the episode and date columns (but keeps the person context).