How to get previous row in Matrix

[MarshalSK]

Hi Folks, Need your expertise to get the previous row if present row is null using DAX  (for each region).

Region	Category	Sub-Category	Profit	YearMonth	Sales
East	A	A1	0	202312	100
East	A	A3	1	202401	300
East	B	B1	2	202402
South	C	C1	3	202312	2100
South	C	C4	4	202401
South	C	C3	5	202402	2300
North	C	C5	6	202312
North	C	C2	7	202401	2200
North	B	B2	8	202402	1200
West	A	A4	9	202312	400
West	A	A2	10	202401

[TheoC]

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 🙂

[MarshalSK]

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 ? 

[TheoC · Accepted solution]

@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

[MarshalSK]

Can you provide above solution column2 for Power query . pls

[MarshalSK]

Perfect. Thanks @TheoC