Problem with cumulative line chart

[ObsidianMagne]

Hi all

I'm trying to create a line chart in order to compare sales data for the same period but for different years. The thing is that I need different lines for each year. I've read many posts and tried different proposed solutions but nothing. I think that maybe the problem is that in the sales file there is a different line for each sale so the same date appears multiple times. I have a date table with date hierarchy, tried to create a different measure for the sum of each year, move years to the legend but  I can't manage to make it work. 

Here is an example of the data, well Ideally I need also slicers for products and distribution channel but that's something

that I will have to deal after I manage to create the chart

In my model I use a date table with data from 2020 to 2035 which I have linked to the sales table 

and tried different apporoaches such as

          CALCULATE(

          SUM(sales[gwp],

          FILTER(ALL(sales),

         sales[date]<=MAX(sales[date])

           ))

but the closest I've got is like this

I've even tried different measures for the begining and the end of the period such as

Last_date=LASTDATE(sales[date])

Last_date_PY =EDATE(MAX(sales[date],-12)

and then tried to combine them with calculate and datesbetween or tried datesytd but nothing

Could you please help?

[Ashish_Mathur]

Hi,

So what problem are you facing?  Share data in a format that can be pasted in an MS Excel file.  Show the expected result in a Table format.  From there, we can build any visual.

[ObsidianMagne]

Hi ashish

Below is a sample of the data I use. Right now I build the visual in excel but I need to change it to power bi.

The visual shoul look something like this for each distribution channel (I will use a slicer for that), at the moment it is a weekly report

 Thanks a lot for the help

channel	DATE	sum
MULTIAGENTS	2/1/2024	68,40
MULTIAGENTS	2/1/2024	22,8
MULTIAGENTS	2/1/2024	68,4
AGENTS	2/1/2024	246,7986
AGENTS	3/1/2024	456
AGENTS	3/1/2024	11,894
AGENTS	4/1/2024	204,9948
AGENTS	9/1/2024	205,029
BROKERS	13/1/2024	34,96
BROKERS	26/1/2024	498,427
AGENTS	26/1/2024	333,431
AGENTS	26/1/2024	11,894
AGENTS	26/1/2024	197,4214
AGENTS	26/1/2024	76,304
AGENTS	26/1/2024	76,304
AGENTS	29/1/2024	120,3498
AGENTS	29/1/2024	107,5666
AGENTS	29/1/2024	22,8
BANCASSURANCE	7/2/2024	45,41
BANCASSURANCE	7/2/2024	45,4138
BANCASSURANCE	7/2/2024	45,4138
BANCASSURANCE	7/2/2024	50,9314
BANCASSURANCE	7/2/2024	45,4138
BANCASSURANCE	7/2/2024	50,9314
BANCASSURANCE	8/2/2024	50,9314
MULTIAGENTS	13/2/2024	22,8
BROKERS	1/3/2024	14,44
BROKERS	1/3/2024	439,4966
BROKERS	14/3/2024	74,8182
AGENTS	14/3/2024	14,44
AGENTS	14/3/2024	14,44
AGENTS	14/3/2024	1900
AGENTS	14/3/2024	228,5016
AGENTS	15/3/2024	456
AGENTS	19/3/2024	22,8
AGENTS	19/3/2024	14,44
MULTIAGENTS	20/3/2024	320,682
AGENTS	20/3/2024	303,9658
AGENTS	21/3/2024	95,7258
AGENTS	22/3/2024	264,5446
MULTIAGENTS	23/3/2024	180,9674
AGENTS	26/3/2024	265,1146
AGENTS	26/3/2024	177,7792
MULTIAGENTS	12/5/2023	430,3386
BROKERS	23/1/2023	532,6916
BROKERS	31/3/2023	186,27
MULTIAGENTS	28/6/2023	476,8088
MULTIAGENTS	28/6/2023	611,8988
MULTIAGENTS	30/10/2023	701,9056
MULTIAGENTS	16/10/2023	321,4572
MULTIAGENTS	28/3/2023	395,6142
MULTIAGENTS	17/2/2023	232,7462
MULTIAGENTS	10/7/2023	395,6104
MULTIAGENTS	28/11/2023	695,2138
MULTIAGENTS	20/6/2023	161,79
MULTIAGENTS	3/1/2023	510,7504
MULTIAGENTS	21/10/2023	34,96
MULTIAGENTS	21/10/2023	34,96
MULTIAGENTS	21/10/2023	34,96
BROKERS	2/2/2023	1923,716
BROKERS	9/1/2023	699,52
BROKERS	8/3/2023	731,25
BROKERS	9/5/2023	238,1536
BROKERS	13/7/2023	731,23
BROKERS	30/6/2023	65,4664
AGENTS	21/7/2023	153,2008
MULTIAGENTS	21/7/2023	153,1666
MULTIAGENTS	8/9/2023	230,4852
AGENTS	28/4/2023	132,0462
AGENTS	19/9/2023	341,0196
AGENTS	4/8/2023	205,2456
AGENTS	25/8/2023	379,9848
AGENTS	15/2/2023	261,6604
AGENTS	20/2/2023	71,6034
AGENTS	17/2/2023	464,2498
AGENTS	15/2/2023	130,8758
AGENTS	16/1/2023	168,0968
AGENTS	26/7/2023	71,6034
AGENTS	1/12/2023	306,0672
AGENTS	20/12/2023	76,304
AGENTS	3/4/2023	134,4744
AGENTS	21/3/2023	213,104
AGENTS	16/3/2023	119,662
AGENTS	28/3/2023	259,7642
AGENTS	16/3/2023	123,3062
AGENTS	6/7/2023	193,6594
AGENTS	14/4/2023	309,7266
AGENTS	25/12/2023	456
AGENTS	21/3/2023	143,07
AGENTS	29/3/2023	719,2108
AGENTS	27/6/2023	1264,192
AGENTS	27/9/2023	147,4248
AGENTS	14/9/2023	201,685

[Ashish_Mathur]

Hi,

This is the best i can do.  PBI file attached.

Hope this helps.

[ObsidianMagne]

Not really but thanks anyway Ashish

Regards

[lbendlin · Accepted solution]

you can refine it as needed.

[ObsidianMagne]

Thank you so much

Regards

[lbendlin]

you can do that much simpler.  Use the year column from your clendar table as the legend.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ObsidianMagne]

Hi Ibendlin

Thanks a lot for the help. I've provided a sample data and the disired outcome as a reply to the Ashish Mathur