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:

AccountQuarterLOBRank Quarter
ABC Inc.20Q1Commercial1
ABC Inc.20Q2Commercial2
ABC Inc.20Q4Contract4
ABC Inc.21Q1Contract5
ABC Inc.21Q2Contract6

 

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."

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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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))

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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    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