Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
Solved! Go to 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
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
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]
)
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
75 | |
60 | |
37 | |
33 |
User | Count |
---|---|
102 | |
56 | |
52 | |
46 | |
40 |