Forum Discussion

MarshalSK's avatar
MarshalSK
Resolver I
2 years ago
Solved

How to get previous row in Matrix

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

RegionCategorySub-CategoryProfitYearMonthSales
EastAA10202312100
EastAA31202401300
EastBB12202402 
SouthCC132023122100
SouthCC44202401 
SouthCC352024022300
NorthCC56202312 
NorthCC272024012200
NorthBB282024021200
WestAA49202312400
WestAA210202401 
Need Help
  • TheoC's avatar
    TheoC
    2 years ago

    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

     

     

5 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    2 years ago

    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's avatar
      MarshalSK
      Resolver I
      2 years ago

      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's avatar
        TheoC
        Community Champion
        2 years ago

        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