cancel
Showing results for
Did you mean:
New Member

## Showing cumulative sales for the last 12 months of each represented month in x axis

Hi,

I am trying to build a bar chart on Power BI that shows the cumulative sum of sales by month, for the last 12 months on each month.

An example of the data is the below:

 Date Sales 1/1/2018 125 2/1/2018 250 3/1/2018 300 4/1/2018 340 5/1/2018 320 6/1/2018 300 7/1/2018 310 8/1/2018 270 9/1/2018 215 10/1/2018 200 11/1/2018 500 12/1/2018 300 1/1/2019 500 2/1/2019 520 3/1/2019 500 4/1/2019 496 5/1/2019 456 6/1/2019 478 7/1/2019 349 8/1/2019 543 9/1/2019 674 10/1/2019 578 11/1/2019 600 12/1/2019 590

I need the output to be displayed for the 2019 year as per below bar chart:

Whereby each value represented on the bar chart is as below:

- Cumulative sales in December 2019 = Sum(sales from January 2019 till December 2019)

- Cumulative sales in November 2019 = Sum(sales from December 2018 till November 2019)

and so on.

I have tried many solutions posted on the forum, but none of them gave me the result I need.

Thanks,

Antoinette

1 ACCEPTED SOLUTION
Community Support

It's not hard to achieve, we only need to create 2 measures as follows

``_total_sum = SUM('Table'[Sales])``
``````__cumulative sales(12months)_1 =
SUMX (
DATESINPERIOD ( 'Table'[Date], FIRSTDATE ( 'Table'[Date] ), -12, MONTH ),
'Table'[_total_sum]
)
``````

Or you can use this measure

``````_cumulative sales(12months)_2 =
CALCULATE (
'Table'[_total_sum],
DATESINPERIOD ( 'Table'[Date], FIRSTDATE ( 'Table'[Date] ), -12, MONTH )
)
``````

Sample:

Result:

Please check the sample file I attached below.

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

It's not hard to achieve, we only need to create 2 measures as follows

``_total_sum = SUM('Table'[Sales])``
``````__cumulative sales(12months)_1 =
SUMX (
DATESINPERIOD ( 'Table'[Date], FIRSTDATE ( 'Table'[Date] ), -12, MONTH ),
'Table'[_total_sum]
)
``````

Or you can use this measure

``````_cumulative sales(12months)_2 =
CALCULATE (
'Table'[_total_sum],
DATESINPERIOD ( 'Table'[Date], FIRSTDATE ( 'Table'[Date] ), -12, MONTH )
)
``````

Sample:

Result:

Please check the sample file I attached below.

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

Please check the below picture and the sample pbix file's link down below.

the measure is in the sample pbix file.

Sales Running total 12 months =
SUMX (
DATESINPERIOD ( Dates[date], LASTDATE ( Dates[date] ), -12, MONTH ),
Sales[Sales total]
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Community Champion

this is pretty easy to do with the TOTALYTD function in DAX.

Try the following measure:

``````YTD Sales =
TOTALYTD(
SUM( myTable[Sales] ),
'Date'[Date]
)``````

Be aware that you need an own date table for all time intelligence functions.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Announcements