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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MarshalSK
Resolver I
Resolver I

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

MarshalSK_0-1705438877163.png

RegionCategorySub-CategoryProfitYearMonthSales
EastAA10202312100
EastAA31202401300
EastBB12202402 
SouthCC132023122100
SouthCC44202401 
SouthCC352024022300
NorthCC56202312 
NorthCC272024012200
NorthBB282024021200
WestAA49202312400
WestAA210202401 
1 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]
)

 

 

TheoC_0-1705440910912.png

 

Hope this helps mate! 🙂

 

Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
TheoC
Super User
Super User

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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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

 

 

TheoC_0-1705440910912.png

 

Hope this helps mate! 🙂

 

Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Can you provide above solution column2 for Power query . pls

Perfect. Thanks @TheoC 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.