Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1638346095704.png

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


Best Regards

Lucien

 

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1638346095704.png

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


Best Regards

Lucien

 

Anonymous
Not applicable

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

@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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.