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 ,

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

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 different in my real data.

  • 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.