Forum Discussion

O20's avatar
O20
New Member
6 years ago

Running Total for VAR table

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?

Need Help

4 Replies

    • O20's avatar
      O20
      New Member
      6 years ago

      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?

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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's avatar
      O20
      New Member
      6 years ago

      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!