Running total in Area Chart with lines all the way to the right

[jaimerfelipe]

Hi all,

I am trying to create a multiple area chart that shows running totals even without data points for the end of the period.
For example, suposing that I want to generate the running total sales broken down per department (multiple areas in the chart) and that a specific department did not have any sales in the past month (but other departments had).
I was able to generate the running total sales broken down by department, but if a department stopped having sales, the lines just stops at the last sale date of that department.
I am using this formula to calculate the running total:
CumSales =
      CALCULATE(
            SUM(Sales[VlSale]),
            FILTER(
                  ALLSELECTED(Sales[DtSale]),
                        Sales[DtSale] <= MAX(Sales[DtSale])
            )
      )

The Area Chart has DtSale on X-Axis, CumSales as Values and Department as Legend.

My chart looks like this:

 

Is there a way to make the running total lines go all the way to the right of the graph (end of period)?

Complementing... I am using Direct Query with SQL Server 2016,

Thanks for your help.

Regards,
Jaime

[v-ljerr-msft · Accepted solution]

Hi @jaimerfelipe,

In your scenario, you may need to add an individual Calendar table at your source side if you done have one yet, and create a relationship between your Sales table and it.

Assume the Calendar table called "Date", you can change [CumSales ] as below.

CumSales = 
      CALCULATE(
            SUM(Sales[VlSale]),
            FILTER(
                  ALLSELECTED('Date'[Date]),
                        'Date'[Date] <= MAX('Date'[Date])
            )
      )

And then you should be able to use the Date column from the Date table as Axis of the Area Chart and Slicer on your report to get the expected result. 

Regards

[jaimerfelipe]

Wow! It worked!

Thanks a lot!