Forum Discussion
How to get previous row in Matrix
MarshalSK correct.
I'm thinking I misread the request but you can use the below to achieve the outcome you want rather than the first solution I proposed (Column 2). Just adjust the name of your table and columns to match yours and you will get the output that matches your initial post.
Column 2 = IF( ISBLANK ( Table2[Sales] ) , CALCULATE( MAX( 'Table2'[Sales] ) , FILTER ( Table2 , [Region] = EARLIER ( [Region] ) && [YearMonth] < EARLIER ( [YearMonth] ) ) ) , [Sales] )Hope this helps mate! 🙂
Theo
Hi MarshalSK
You can create a calculated column and use CALCULATE & MAX with ALLEXCEPT.
Column =
CALCULATE (
MAX ( 'Table'[Sales] ) ,
ALLEXCEPT ( 'Table' , 'Table'[Region] )
)
Hope this helps.
Theo 🙂
Thanks for the reply TheoC .
just a quick question, if the present value is NULL, above calculation will get previous value based on the same region ?
MarshalSK correct.
I'm thinking I misread the request but you can use the below to achieve the outcome you want rather than the first solution I proposed (Column 2). Just adjust the name of your table and columns to match yours and you will get the output that matches your initial post.
Column 2 = IF( ISBLANK ( Table2[Sales] ) , CALCULATE( MAX( 'Table2'[Sales] ) , FILTER ( Table2 , [Region] = EARLIER ( [Region] ) && [YearMonth] < EARLIER ( [YearMonth] ) ) ) , [Sales] )Hope this helps mate! 🙂
Theo