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

Aj_BI's avatar
Aj_BI
Frequent Visitor
6 years ago

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

 

Please advice.

 
  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      Aj_BI
      Frequent Visitor
      6 years ago

      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: