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.

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: