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

[Anonymous]

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.

Can you please advise?

Thanks,

Antoinette

[v-angzheng-msft · Accepted solution]

 Hi, @Anonymous 

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.

[Jihwan_Kim]

Hi, @Anonymous 

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]

)

https://www.dropbox.com/s/0j171p243qx9psb/amouawad.pbix?dl=0 

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.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[selimovd]

Hey @Anonymous ,

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic