Forum Discussion

igaca's avatar
igaca
Helper III
9 years ago
Solved

Select Previous Row (Not utilizing Time Intelligence)

  Background:  I have a number of files each containing different levels of progress as based on manhours earned quantity as a portion of that budgeted (this is the measure titled "AGP % Comp" = DIV...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

     

    igaca

     

    You have an index column, well using this you can get the previous value:

     

    PrevValue =
VAR previndex =
    VALUES ( Table1[Index] ) - 1
RETURN
    CALCULATE ( SUM ( Table1[Value] ) )
        - CALCULATE (
            SUM ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = previndex )
        )

    Modified this to your tables and measures.

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

 

igaca

 

You have an index column, well using this you can get the previous value:

 

PrevValue =
VAR previndex =
    VALUES ( Table1[Index] ) - 1
RETURN
    CALCULATE ( SUM ( Table1[Value] ) )
        - CALCULATE (
            SUM ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = previndex )
        )

Modified this to your tables and measures.

igaca's avatar
igaca
Helper III
9 years ago

Victor,

 

I started learning PowerPivot/DAX last December, and at some point along that road I came across this article by Alberto Ferrari

 

https://www.sqlbi.com/articles/variables-in-dax/

 

However, at that time I hadn't the slightest clue what he was talking about.  I had long since forgotten about that article ... then you come along and reintroduce me to DAX variables; thank you sir.  This is significantly more useful than solving this particular issue :)

 

On another note, I am still very much curious how EARLIER() functions might be effectively implemented in this instance, so if you or anyone else has any words of wisdom, I would greatly appreciate it.

 

Cheers,

 

IG