Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return previous value when date difference > 1

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 ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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))