How to display cummulative sales by month in a line chart?

[squarecat]

Hello guys, I hope someone can help me figure this out.

First of all, the calendar in my company starts on October and ends on September. I am working with 3 years of information, sales from 21-22, sales from 22-23 and sales from the current year 23-24. Each table contains the following columns: date, client code, client name, product code, product name and sales amount.

I have to create a line chart that shows the cummulative sales amount by month for each year, It should be something like this:

As the data tables I'm working with contain more than one sale for each date, I decided to create 3 additional tables (one for each year I'm working with) that summarize the total sales amount by every date. Here is the expression I used for the year 21-22 and addecuated for the other years.

SUMM 21-22 =
SUMMARIZE (
    '21 22_Sheet',
    '21 22_Sheet'[Date],
    "Total Sales", SUM ( '21 22_Sheet'[Sales Amount] )
)

Then I created a calculated column for the cummulative sales using this expression:

21 22 Sales Acum =
CALCULATE (
    SUM ( 'SUMM 21-22'[Total Sales] ),
    ALL ( 'SUMM 21-22' ),
    'SUMM 21-22'[Date] <= EARLIER ( 'SUMM 21-22'[Date] )
)

Then I went in report view to create the line chart using the custom calendar table my company uses for the x axis and the cummulative sales for each year on the y axis. The problem is that it is not displaying the information correctly, for example, it shows that the sales amount for March is reaching the billions when in reality the correct sales amount for that month should be around 36-38M.

Can anyone tell me what am I doing wrong or if there is another way to create the chart I need?

In the link below I'm attaching the PBI file and the data in Excel so you can check it out more easily. Thanks for any help in advance.

https://1drv.ms/f/s!Aki9Zu5XNsuxhkjntg8AQWBDr7Nj?e=m9EZ6Q 

[Anonymous · Accepted solution]

Hi @squarecat ,

We can create measures.

Measure 21 22 = CALCULATE(SUM('SUMM 21-22'[Total Sales]),FILTER(ALL('SUMM 21-22'),'SUMM 21-22'[Date] <= MAX('SUMM 21-22'[Date])))

Measure 22 23 = CALCULATE(SUM('SUMM 22-23'[Total Sales]),FILTER(ALL('SUMM 22-23'),'SUMM 22-23'[Date] <= max('SUMM 22-23'[Date])))

Measure 23 24 = CALCULATE(SUM('SUMM 23-24'[Total Sales]),FILTER(ALL('SUMM 23-24'),'SUMM 23-24'[Date] <= max('SUMM 23-24'[Date])))

Then the result is as follows.

Best Regards,

Neeko Tang

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

[squarecat]

This is exactly what I wanted. Thank you very much!

[HotChilli]

I appreciate you providing the pbix. 

The cumulative sales has been created as a calculated column so when the field is added to the chart, it uses an implicit measure(in this case SUM) and adds up all the cumulative values for each day and plots it for the month.  That gives a very large number because all the values are counted multiple times.

Your options: Write a measure for each table and return the column value at the end of the month

OR

write a measure which returns the cumulative sales for the month (that's going to be just a slight change to the existing calculated column DAX).

Hope that helps

[squarecat]

Thank you very much for replying. If it isn't a lot of trouble can you provide the DAX for the first measure? I would appreciate it a lot even if you use generic names for the expression.