How to accumulate sales, even for missing year-month

[alexbjorlig]

My objective is to create a measure for accumulate sales, even for months with no sales.

With the given date-table:

Calendar

Date	MonthYear
1/1/2017	2017-01
2/1/2017	2017-01
...
1/2/2017	2017-02
1/3/2017	2017-03
1/4/2017	2017-04

SalesTable

date	Sales
1/1/2017	200
1/2/2017	300

How do I get this output:

Month Year	Accumulated Sales
2017-01	200
2017-02	500
2017-03	500
2017-04	500

Thank you so much for taking a look at this. 

What I have tried:

SUMX(
    VALUES('Calendar'[MonthYear]),
    VAR MAX_MONTH = MAX('Calendar'[MonthYear]) RETURN
    CALCULATE(SUM('SalesTable'[Sales]) + 0, 'Calendar'[MonthYear] <= MAX_MONTH
))

The hard part for me is getting values, even for year-months with no sales (and somehow preserving the row context).

[AlexisOlson · Accepted solution]

If you're using the calendar table for the rows in your visual, then this should be a standard cumulative total regardless of gaps in the sales fact table.

CALCULATE (
    SUM ( 'SalesTable'[Sales] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[MonthYear] <= MAX ( 'Calendar'[MonthYear] )
    )
)

[smpa01]

@alexbjorlig  if you have the following Fact table

sales =
VAR _date =
    MAX ( 'Calendar'[Calendar.Date] )
VAR _runningTotal =
    CALCULATE (
        SUM ( 'Fact'[Fact.Sales] ),
        ALL ( 'Calendar' ),
        'Calendar'[Calendar.Date] <= _date
    )
RETURN
    _runningTotal

Question - Do you want the subtotal level to be rolled up as well? e.g. in this example ->200+4*500+1100=3300 or 1100 is the correct toal desired?

[AlexisOlson]

What is the _sum variable defined for?