Forum Discussion

Aj_BI's avatar
Aj_BI
Frequent Visitor
6 years ago
Solved

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(...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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,
    Icey

     

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

Icey's avatar
Icey
Community Support
6 years ago

Hi Aj_BI ,

All the three expressions above are Measure.

And, what's this?

 

 

Best Regards,

Icey

Aj_BI's avatar
Aj_BI
Frequent Visitor
6 years ago

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.