DAX - get most recent value from any previous row based on 2 filters

[TCavins]

I have a table similar to the following

PERSON ID	CALENDAR ID	STATUS	RATE	MostRecentOpenRate
1	1	VOID	3.00
1	2	OPEN	4.50
1	3	BUSY	2.00	4.50
2	1	OPEN	6.50
2	2	DOWN	3.00	6.50
2	3	BUSY	2.00	6.50
2	4	OPEN	5.50
2	5	BUSY	1.75	5.50

For each row, I want a column to give me the the most recent Rate for the same person where Status = OPEN from an earlier Calendar ID. The table I have is already sorted by person and calendar ID.

I've shown the column I want above for MostRecentOpenRate. How can I use DAX to calculate the value for MostRecentOpenRate?

[v-jianpeng-msft]

Your solution is so great Jihwan_Kim  and Ashish_Mathur

Hi, @TCavins 

Thank you so much for raising your questions in the community! If Super user's reply solves your doubt, you can mark it as a "solution" so that other friends who encounter similar problems can also find the answer. This will go a long way in helping others in the community. Thank you again for participating!

Best Regards

Jianpeng Li

[Ashish_Mathur]

Hi,

Use this calculated column formula

Column = if(Data[STATUS]="Open",BLANK(),LOOKUPVALUE(Data[RATE],Data[CALENDAR ID],CALCULATE(MAX(Data[CALENDAR ID]),FILTER(Data,Data[PERSON ID]=EARLIER(Data[PERSON ID])&&Data[CALENDAR ID]<EARLIER(Data[CALENDAR ID])&&Data[STATUS]="Open")),Data[PERSON ID],Data[PERSON ID]))

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

It is for creating a calculated column.

MostRecentOpenRate CC =
VAR _personid = Data[PERSON ID]
VAR _calendarid = Data[CALENDAR ID]
VAR _recentopencalendarid =
    MAXX (
        FILTER (
            Data,
            Data[PERSON ID] = _personid
                && Data[CALENDAR ID] < _calendarid
                && Data[STATUS] = "OPEN"
        ),
        Data[CALENDAR ID]
    )
RETURN
    IF (
        NOT Data[STATUS] = "OPEN",
        MAXX (
            FILTER (
                Data,
                Data[PERSON ID] = _personid
                    && Data[CALENDAR ID] = _recentopencalendarid
            ),
            Data[RATE]
        )
    )

[TCavins]

Thank you for your response! It's pretty close to where I was going using variables instead of EARLIER, however I was not using MAXX and I think this reads easier that what I was attempting to do.