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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!