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

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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