cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
igaca
Helper III
Helper III

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?

 

Here's is the summary table:

 

Report Summary TableReport Summary Table

Thanks in advance for any constructive input!

 

Regards,

 

Igor

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

 

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




Lima - Peru

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
Community Champion

 

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




Lima - Peru

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!