Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
alexbjorlig
Helper IV
Helper IV

How to accumulate sales, even for missing year-month

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

 

dateSales
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).

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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] )
    )
)

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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
Super User
Super User

@alexbjorlig  if you have the following Fact table

 

smpa01_0-1633637480389.png

 

smpa01_0-1633637571476.png

 

 

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?

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

What is the _sum variable defined for?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.