Cumulative Sales

[vmakhija]

Hello

I am trying to create a Stacked bar chart which will have cumulative sales over months i.e. Jan will have Jan, Feb will have Jan + Feb, Mar will have Jan + Feb + Mar, Apr will have Jan + Feb + Mar + Apr and so on....

The measure to be used for this should also be responsive to slicer filters.

I have created the following measure which works fine for normal bar chart -

RunningTotal = CALCULATE(
SUM( Orders[Sales] ),
FILTER( ALLSELECTED(Orders) ,
SUMX( FILTER( Orders, EARLIER( Orders[Order Date] ) <= Orders[Order Date] ), Orders[Sales] )
)
)

However, when I turn it to Stacked column chart and put some field (like Product Category) in Column Series, it gives incorrect results.

The values for each category repeats.

Below is screenshot of what I am trying to explain - first chart is without stacks and 2nd chart is with stacks of Category.

Any direction will be helpful.

[Ashish_Mathur]

Hi,

Does this work?

=CALCULATE(SUM(Orders[Sales]),DATESYTD(Calendar[Date],"31/12"))

There should be a Calendar table and a relationship from the Date column in the Orders Table to the Date column in the Calendar table.  Ensure that you drag Month from the Calendar Table to your visual.

Hope this helps.

[vmakhija]

@Ashish_Mathur

Thanks for this solution. It works great partially.

The only thing is I want to cumulate it over the years i.e. it shouldn't restart from Jan of next year.

I want that Jan should be cumulative of last year's sales too.

This is because users will see the sales from lets say Oct of 2017 to Sep of 2018.

Any idea on this?

I thought of DATESBETWEEN, but that will not serve the purpose.

[vmakhija]

Well, I tried this and think it will work fine -

NEWMEASURE = CALCULATE(SUM(Orders[Sales]),DATESBETWEEN('Date'[Date],DATE(2007,6,1),MAX('Date'[Date])))

Above is considering my minimum date will not be before June 01, 2007

I created quick visual to test it and it seems to be fine.

[Ashish_Mathur · Accepted solution]

Hi,

You may try this

=CALCULATE(SUM(Orders[Sales]),DATESBETWEEN('Date'[Date],MINX(ALL(Calendar),Calendar[Date]),MAX('Date'[Date])))

This way the start date will be determined automatically.