Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have two tables. One is "Sales" table and the other one is "Period" table. In the "Sales" table, I have "Product", "Sales Amount", and "Dates" columns. In this table have data for Jan, Feb, and Apr. In the "Period" table, I have "Dates" column which contains data from Jan to Dec. I have calculated cumulative sum with the following formula:
Cumulative Sales =
CALCULATE (
SUM ( 'Sales'[Sales Amount ] ),
FILTER (
ALL(Sales),
Sales[Dates]<= MAX(Sales[Dates])
)
)
I want to have a column chart with dates in "Sales" table and cumulative sales. But I want a slicer build on "Period" table, which shows cumulative sum when I select a specific date from that slicer. Now when I select March 2020 from the slicer, nothing appears in the column chart since there is no data for March in my "Sales" table. But I want it to show cumulative sum for March. Please see the pictures.
Thanks in advance!
Sales table
Period table
The visual
No data when March selected
Hi @Anonymous ,
You may create relationship between the tables Sales and Period with "Both" Cross filter direction, then change the function ALL to ALLSELECTED in your formula.
Cumulative Sales =
CALCULATE (
SUM ( 'Sales'[Sales Amount ] ),
FILTER (
ALLSELECTED(Sales),
Sales[Dates]<= MAX(Sales[Dates])
)
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
It's obvious, if the selected month has no value the visual will be empty.
If you want the visual to appear with no data you have to edit your measure by adding +0 at the end.
Cumulative Sales = CALCULATE (SUM ( 'Sales'[Sales Amount ] ),FILTER (ALL(Sales),Sales[Dates]<= MAX(Sales[Dates])))+0
Does it work ? Mark it as a solution
A kudos would be appreciated
@Anonymous Assuming you have a poper calendar table and a relationship between the calendar table and sales, you caan use a measure like this. See a picture below how it appears - you may need to put a year filter.
For the complete pattern, see this article:
https://www.daxpatterns.com/cumulative-total/
Cumulative Sales 1 =
IF (MIN ( 'Date Table'[Date] )
<= CALCULATE ( MAX ( Sales[Dates] ), ALL ( Sales ) ),
CALCULATE (
SUM ( 'Sales'[Sales Amount] ),
FILTER (
ALL('Date Table'[Date]),
'Date Table'[Date]<= MAX('Date Table'[Date]))
)
)