topic Cumulative Sum filtered by Year in DAX Commands and Tips

Cumulative Sum filtered by Year

Danimonti91 — Tue, 01 Nov 2022 10:23:52 GMT

Good morning guys,

I am struggling with a DAX. I am trying to build a line chart that should contain 5 lines for 5 different years (From 2017 to 2022) and one line that should be the Average of previous 5 years excluding 2022. What I am trying to do is to have a cumulative sum for every year so that I will have 5 different dax already filtered by year and in the X axis I can put months or quarters.

I am using this code for the cumulative sum :

1_CumulativeAmount_2021_test = CALCULATE(

SUM('RE_Investment'[1_Amount_Adjusted]),

FILTER(

ALLSELECTED('RE_Investment'),

'RE_Investment'[Date]<=MAX('RE_Investment'[Date]) && 'RE_Investment'[Year] =MAX('RE_Investment'[Year])

))

Inside this dax I would like to add a filter in order to have only one year but I tried some different approach without results.

Do you have an idea about how to do it?

Thanks a lot!

Re: Cumulative Sum filtered by Year

amitchandak — Tue, 01 Nov 2022 13:53:30 GMT

Danimonti91 , you can try YTD with date table, Date in visual should also come from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Cumulative Sum filtered by Year

v-zhangti — Wed, 02 Nov 2022 06:18:05 GMT

Hi, Danimonti91

Can you simplify your data and provide some sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative Sum filtered by Year

Danimonti91 — Wed, 02 Nov 2022 10:31:33 GMT

Thanks for the reply, imagine to have a table similar to the one below (I just put dummy data for an example). From this table I want to have a line chart, showing Sales for every year (Cumulative) and on the X axis I want the months or quarters. Additionally I also want the average of last years. So the chart in this case needs to have 2019 Cumulative, 2020 Cumulative, 2021 Cumulative, 2022 Cumulative and AVG from 2019 to 2021 with Months or Quarters on the X axis.

Thanks a lot in advance

Date	Sales
1/1/2019	15
10/2/2019	7
22/3/2019	98
29/10/2019	14
1/1/2020	1
7/4/2020	34
8/10/2020	57
18/12/2020	2
3/2/2021	35
8/4/2021	23
10/8/2021	45
29/11/2021	67
7/2/2022	21
11/7/2022	45
18/10/2022	22
29/10/2022	98

Re: Cumulative Sum filtered by Year

v-zhangti — Tue, 08 Nov 2022 09:31:31 GMT

Hi, Danimonti91

You can try the following methods.

Column:

Year = YEAR([Date])

Month = MONTH([Date])

Cumulative sum = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Year] = EARLIER ( 'Table'[Year] ) && [Month] <= EARLIER ( 'Table'[Month] ) ) )

Average = CALCULATE ( AVERAGE ( 'Table'[Sales] ), FILTER ( 'Table', [Year] = EARLIER ( 'Table'[Year] ) ) )

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.