Forum Discussion
DAX Query to Find Next Value
Hi All,
I am new in PowerBI and need your help!
I have a table like below having the Date Time Stamp as first column with Sales in second column. What I need is a formula to compute a third column(Sales-1) which can give me values from Sales column but first record as blank and 2nd record should show 1st record of Sales column. Similarly, I need another column as Sales-2 and so on.
Point to be noted: I am using Direct Query so I can not use any Index column from M Query/Query Editor Page. Thanks in advance for your help.
| Date Time | Sales | Sales-1 | Sales-2 | Sales-3 | Sales-4 | Sales-5 |
| 20190101 | 5000 | |||||
| 20190102 | 4100 | 5000 | ||||
| 20190103 | 3400 | 4100 | 5000 | |||
| 20190104 | 2300 | 3400 | 4100 | 5000 | ||
| 20190105 | 8800 | 2300 | 3400 | 4100 | 5000 | |
| 20190106 | 2500 | 8800 | 2300 | 3400 | 4100 | 5000 |
Hi Aj_BI ,
Try this:
Rank = RANKX ( ALL ( TableTest ), CALCULATE ( MAX ( TableTest[Date_Time] ) ), , ASC, DENSE )Sales-1 = VAR index = [Rank] - 1 RETURN CALCULATE ( SUM ( TableTest[Sales] ), FILTER ( ALL ( TableTest ), index = [Rank] ) )Sales-2 = VAR index = [Rank] - 2 RETURN CALCULATE ( SUM ( TableTest[Sales] ), FILTER ( ALL ( TableTest ), index = [Rank] ) )Best Regards,
IceyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi Aj_BI ,
Try this:
Rank = RANKX ( ALL ( TableTest ), CALCULATE ( MAX ( TableTest[Date_Time] ) ), , ASC, DENSE )Sales-1 = VAR index = [Rank] - 1 RETURN CALCULATE ( SUM ( TableTest[Sales] ), FILTER ( ALL ( TableTest ), index = [Rank] ) )Sales-2 = VAR index = [Rank] - 2 RETURN CALCULATE ( SUM ( TableTest[Sales] ), FILTER ( ALL ( TableTest ), index = [Rank] ) )Best Regards,
IceyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey Icey and AlB AIB, first thanks a lot for your help and time. I really appreciate that.
@Icey: you solution looks very precise and easy to undertand but somehow, I am not able to get the RANK. I also tried to add an index column from Query Editor but not workign for me due to Direct Query.
When I try to use CALCULATE to get the RANK in a measure(I hope you are too creating a measure as RANK, and I can not use calcualted column due to error to use CALCULATE), I get weired error. Next I tried applying some work arounds but those are giving me same RANK as 1. Not sure what is different in my real data.
Hi Aj_BI
Try this for Sales-1
Sales-1 = VAR PreviousDate_ = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < EARLIER ( Table1[Date] ), ALL ( Table1 ) ) RETURN CALCULATE ( DISTINCT ( Table1[Sales] ), Table1[Date] = PreviousDate_, ALL ( Table1 ) )For the others you can follow exactly the same pattern, using the previous column. For instance, for Sales-2 you'd use Sales-1 in the second CALCULATE, and so on:
Sales-2 = VAR PreviousDate_ = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < EARLIER ( Table1[Date] ), ALL ( Table1 ) ) RETURN CALCULATE ( DISTINCT ( Table1[Sales-1] ), Table1[Date] = PreviousDate_, ALL ( Table1 ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Thank you so much AIB for your help. I tried the same but got the error at EARLIER function.
Please advice.
Hi Aj_BI
The code above is for creating calculated columns in your original table. It looks like you are using the code in a visual. You can create the calculated columns and then create a table visual simply showing the columns (all in Don't summarize). See it all at work in the attached file.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
