Building a Table that Calculates across rows and columns

[Anonymous]

Hi all,

I've built a table in excel that can calculate a target amount based on previous inputs, and I'd like to replicate it in BI (since it makes sharing easier and we already have a couple dashboard set up). Here is a screen shot of the table in excel: 

 To calculate column I ("Target Confirmations"), I've added the Fill Pace Remaining (Column L) to the suceeding deadline's Fill Pace (Column G). The basic idea is that if we were supposed to get 5 applications at the first deadline, and we only received 4 applications, then our goal for the next deadline would be the second deadline's original goal +1 (to make up for that deficit). Therefore the "Target Confirmations" for November 16, 2018 is L3 + G4, January 25, 2019 is L4 + G5, and so on. 

The trouble I'm having is that in BI I don't know how to reference a specific value based on a preceeding row. I can subtract, multiply, etc values that are in the same row but different columns, but not in different columns AND rows. I've tried a couple different ways to get a value, but no matter what I try I always end up needing to reference somehow the preceeding deadline as it's values will impact the next deadline. 

Hoping I can get some support/clarification on how to build the report so I can reference preceeding rows, or on how I should be writing measures/calculated columns. 

Thanks in advance!

-Zach

[v-piga-msft · Accepted solution]

Hi @Anonymous,

You could create an index column in Query Editor and then create a calculated column with the formula below.

Column =
VAR a =
 CALCULATE (
 MAX ( 'Table1'[Fill Pace remaining] ),
 FILTER ( 'Table1', 'Table1'[index] = EARLIER ( Table1[index] ) - 1 )
 )
RETURN
 a + 'Table1'[FillPace]

In addition, you could have a good look at this blog about using M query to achieve your desired output in Query Editor.

Best Regards,

Cherry

[Anonymous]

Hey Cherry,

Thanks for the tip here! I tried incorporating the calculated column you suggested, but oddly I did not get the same results. Here's screen shot: 

 Not sure why it's not calculating the rest of the column like in your example. 

Any help would be appreciated!

Thanks,

-Zach