Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
the measure is in the sample pbix file.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |