cancel
Showing results for
Did you mean:
Frequent Visitor

## Running Total for VAR table

I've been trying to calculate a "Months of Supply" similar to what's described here:

I am stuck on the line of code that calculates the Running Total for sales.

My data is structured a little different than the one on the link above. I have monthly data (date is always MM/1/YYYY).

This is where I'm stuck:

VAR __table04 = ADDCOLUMNS(__table03,"DEMAND", SUMX(FILTER(__table03,[Date]<=EARLIER([Date])),[Sales]))

Through some debugging I found out the formula is ignoring EARLIER(). So the result in __table04 is always the SUM of ALL [Sales].
So if I plot the TOP 3 rows of __table04 on a pivot, this is the kind of result I am getting.

 2011 Jan Date: 2/1/2011, 3/1/2011, 4/1/2011 | Sales: 3, 4, 5 | Demand: 1000, 1000, 1000 Feb Date: 3/1/2011, 4/1/2011, 5/1/2011 | Sales: 4, 5, 6 | Demand: 997, 997, 997 Mar Date: 4/1/2011, 5/1/2011, 6/1/2011 | Sales: 5, 6, 7 | Demand: 993, 993, 993

When the expected should be:

 2011 Jan Date: 2/1/2011, 3/1/2011, 4/1/2011 | Sales: 3, 4, 5 | Demand: 3, 7, 12 Feb Date: 3/1/2011, 4/1/2011, 5/1/2011 | Sales: 4, 5, 6 | Demand: 4, 9, 15 Mar Date: 4/1/2011, 5/1/2011, 6/1/2011 | Sales: 5, 6, 7 | Demand: 5, 11, 18

Any ideias?

4 REPLIES 4
Super User IV

## Re: Running Total for VAR table

You can show them in Matrix, Refer

https://community.powerbi.com/t5/Desktop/Cumulative-Subtotals/td-p/395842

Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Community Support Team

## Re: Running Total for VAR table

Hi @O20 ,

You can create a new table to test the results.

```__table04 =
__table03,
"DEMAND", SUMX ( FILTER ( __table03, [Date] <= EARLIER ( [Date] ) ), [Sales] )
)```

OR

```__table04 =
__table03,
"DEMAND", SUMX ( FILTER ( ALL(__table03), [Date] <= EARLIER ( [Date] ) ), [Sales] )
)```
Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Frequent Visitor

## Re: Running Total for VAR table

I am dealing with a table stored in a VAR and not an actual table in my Data Model.

That's where my challenge is.

Any other ideas?

Frequent Visitor

## Re: Running Total for VAR table

I appreciate the response. But this is exaclty the same code I posted on my question saying it wasn't working.

Also the "ALL (__table03)" generates a compiling error.

That code is adding up ALL rows on __table03. So the new column on __table04 results in a Grand Total for [Sales] in __table03 rather than a Running Total.

Any other ideas?

Thank you!

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors