The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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).
Solved! Go to 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] )
)
)
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] )
)
)
@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?
What is the _sum variable defined for?
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |