Return previous value when date difference > 1

[Anonymous]

This is what my data looks like:

Account	Quarter	LOB	Rank Quarter
ABC Inc.	20Q1	Commercial	1
ABC Inc.	20Q2	Commercial	2
ABC Inc.	20Q4	Contract	4
ABC Inc.	21Q1	Contract	5
ABC Inc.	21Q2	Contract	6

I am trying to create a new column that will indicate when an account has changed LOB.  For this example, my hope would be that in a new column, call it "LOB Change Indicator," in the row for 20Q4 the entry would say "LOB Change" because the previous entry was Commercial and now it's Contract.  I haven't been able to get it to work because the account wasn't wrriten in 20Q3, so there is a msising rank and I can't set the lookup to call on "rank quarter - 1."

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Try the below dax to create a new column:

LOB Change =
VAR test1 =
    CALCULATE (
        MAX ( 'Table'[LOB] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = EARLIER ( 'Table'[Account] )
                && 'Table'[Rank Quarter] = EARLIER ( 'Table'[Rank Quarter] )
        )
    )
VAR terst2 =
    CALCULATE (
        MAX ( 'Table'[Rank Quarter] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = EARLIER ( 'Table'[Account] )
                && 'Table'[Rank Quarter] < EARLIER ( 'Table'[Rank Quarter] )
        )
    )
VAR test3 =
    CALCULATE (
        MAX ( 'Table'[LOB] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = EARLIER ( 'Table'[Account] )
                && 'Table'[Rank Quarter] = terst2
        )
    )
RETURN
    IF (
        test3 <> BLANK ()
            && test3 <> 'Table'[LOB],
        "LOB Change Indicator",
        BLANK ()
    )

Return result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

This is not working.  My goal is for each row, to find the LOB associated with the quarter that was just before it.

[amitchandak]

@Anonymous , anew column

Last = var _max = maxx(FILTER(Data, [Account] = EARLIER([Account]) && [Rank Quarter] <EARLIER([Rank Quarter])),[Rank Quarter])
return maxx(FILTER(Data, [Account] = EARLIER([Account]) && [Rank Quarter] = _max),[LOB])

OR

new measure 

Last LOB = var _max = maxx(FILTER(allselected(Data), [Account] = max([Account]) && [Rank Quarter] <max([Rank Quarter])),[Rank Quarter])
return maxx(FILTER(allselected(Data), [Account] = Max([Account]) && [Rank Quarter] = _max),[LOB])

[amitchandak]

@Anonymous , You should create a dense rank 

I usually prefer a separate table for time. 

But a measure like this should help

measure  =

var _max = calculate(max(Table[Qtr]), filter(allselected(Table), Table[Qtr]< max(Table[Qtr]))  )

return 

calculate( max(Table[LOB]), filter(Allselected(Table), Table[Qtr] =_max))