Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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."
Solved! Go to 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
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
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])
@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))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |