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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Aj_BI
Frequent Visitor

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 TimeSalesSales-1Sales-2Sales-3Sales-4Sales-5
201901015000     
2019010241005000    
20190103340041005000   
201901042300340041005000  
2019010588002300340041005000 
20190106250088002300340041005000
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

 50000.PNG

 

Best Regards,
Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Icey
Community Support
Community Support

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

 50000.PNG

 

Best Regards,
Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Aj_BI
Frequent Visitor

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 RANKX Not Working.jpgdifferent in my real data.

Icey
Community Support
Community Support

Hi @Aj_BI ,

All the three expressions above are Measure.

And, what's this?

rank.PNG

 

 

Best Regards,

Icey

Aj_BI
Frequent Visitor

Hi @Icey , Thanks for the follow up, even I was not sure why that error was coming so I deleted and re cretaed a fresh report. Now I got the RANK working but when I try to get the Previous Sale, it's blank. I tried to use ALLSELECTED in FILTER and started getting only 1 record with rest as blank.No Data In Sale Prv.png

AlB
Super User
Super User

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  Datanaut

Aj_BI
Frequent Visitor

Thank you so much AIB for your help. I tried the same but got the error at EARLIER function.

EARLIER Function Error.jpg 

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 Datanaut

 

 

Aj_BI
Frequent Visitor

Thanks so much AIB for your time and help. Looks like things are not in my favor as I mentioned the source of the data is a Direct Query and when I tried creating a computed column, it gives me another error for COMPUTE not working:

Calculate Function Not Allowed in Direct Query.jpg

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors