cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amouawad
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:

 

DateSales
1/1/2018125
2/1/2018250
3/1/2018300
4/1/2018340
5/1/2018320
6/1/2018300
7/1/2018310
8/1/2018270
9/1/2018215
10/1/2018200
11/1/2018500
12/1/2018300
1/1/2019500
2/1/2019520
3/1/2019500
4/1/2019496
5/1/2019456
6/1/2019478
7/1/2019349
8/1/2019543
9/1/2019674
10/1/2019578
11/1/2019600
12/1/2019590

 

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

 

amouawad_1-1618857156262.png

 

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

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

 Hi, @amouawad 

 

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:

v-angzheng-msft_0-1618995075103.jpeg

Result:

v-angzheng-msft_1-1618995075106.jpeg

v-angzheng-msft_2-1618995075110.jpeg

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.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

 Hi, @amouawad 

 

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:

v-angzheng-msft_0-1618995075103.jpeg

Result:

v-angzheng-msft_1-1618995075106.jpeg

v-angzheng-msft_2-1618995075110.jpeg

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.

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @amouawad 

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

the measure is in the sample pbix file.

 

Picture5.png

 

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.


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

selimovd
Community Champion
Community Champion

Hey @amouawad ,

 

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
 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors