Select Previous Row (Not utilizing Time Intelligence)

[igaca]

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" = DIVIDE([Tot. Earned Mhrs],[Tot. Budgeted Mhrs]).  So, for say the "2011.05.23 MM" file, a total of 12,566.73 Mhrs were earned out of 171,079.81 budgeted....or 7.35%

All file data is contained within a single fact table with the file name column ("MM Version") the distinguishing dimension for each set of associated facts.  This table is linked to a Lookup Table containing all "MM Versions", which also contains an Index column for sorting purposes.

What I am trying to do is populate the "AGP % Progress" column with [AGP % Complete] variances for that of the current row to the one before it.  E.G. the value in row 5 should be 13.56% (31.97 - 18.41).  

I've tried to accomplish this is various ways, including that of using EARLIER() within a measure with use of an interator and second FILTER to create a secondary loop for EARLIER to reference, however, this approach -CALCULATE([AGP % Comp],FILTER('MM Version',COUNTROWS(FILTER('(L) MM Version',EARLIER('MM Version'[MM Version])<='MM Version'[MM Version]))))- returns identical results to that of the "AGP % Comp" column while going with -CALCULATE([AGP % Comp],FILTER(ALL('MM Version'),COUNTROWS(FILTER('(L) MM Version',EARLIER('MM Version'[MM Version])<='MM Version'[MM Version]))))- yields the results displayed in "AGP % Progress Cumm" colum on the far right.

I am having no luck in getting it to reference the previous row's context (MM file version).  Any input is greatly appreciated.  I have a hunch that part of the issue is that we're dealing with text values here...if so, perhaps referencing the associated Index column could be the key?

Here's is the summary table:

Report Summary Table

Thanks in advance for any constructive input!

Regards,

Igor

[Vvelarde · Accepted solution]

@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]

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