Running total with date slicer from another table

[Anonymous]

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 tablePeriod tableThe visualNo data when March selected

[v-xicai]

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.

[nandukrishnavs]

@Anonymous 

Create a table with the date values.

Period

Sales

Relationship

Cumilative Sales = TOTALYTD(SUM(Sales[Sales]),Period[Date])

Output

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[DataVitalizer]

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 

[sanimesa]

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

[thephotobus]

• @sanimesa - you really helped me with this post.  Thank you!  Your method worked even when I couldn't get the method from DAXPatterns to do what I needed.