cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Building a Table that Calculates across rows and columns

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.

-Zach

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Building a Table that Calculates across rows and columns

Hi @zmetzinger,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Building a Table that Calculates across rows and columns

Hi @zmetzinger,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Building a Table that Calculates across rows and columns

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