Running Total for VAR table

[O20]

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

 https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656

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?

[v-eachen-msft]

Hi @O20 ,

You can create a new table to test the results.

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

OR

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

[O20]

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!

[amitchandak]

You can show them in Matrix, Refer 

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

[O20]

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?