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" = 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?
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.