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

 

  •  

    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.

2 Replies

  • Vvelarde's avatar
    Vvelarde
    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.

    • igaca's avatar
      igaca
      Helper III

      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