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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. 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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.