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.

AlB's avatar
AlB
Community Champion
6 years ago

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