Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ObsidianMagne
Regular Visitor

Problem with cumulative line chart

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

 

ObsidianMagne_1-1712436313658.png

 

 

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

 

ObsidianMagne_2-1712438087232.png

 

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?

 

 

 

 

1 ACCEPTED SOLUTION

lbendlin_0-1712597030219.png

 

you can refine it as needed.

 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

ObsidianMagne_2-1712564752268.png

 

 Thanks a lot for the help

 

channelDATEsum
MULTIAGENTS2/1/202468,40
MULTIAGENTS2/1/202422,8
MULTIAGENTS2/1/202468,4
AGENTS2/1/2024246,7986
AGENTS3/1/2024456
AGENTS3/1/202411,894
AGENTS4/1/2024204,9948
AGENTS9/1/2024205,029
BROKERS13/1/202434,96
BROKERS26/1/2024498,427
AGENTS26/1/2024333,431
AGENTS26/1/202411,894
AGENTS26/1/2024197,4214
AGENTS26/1/202476,304
AGENTS26/1/202476,304
AGENTS29/1/2024120,3498
AGENTS29/1/2024107,5666
AGENTS29/1/202422,8
BANCASSURANCE7/2/202445,41
BANCASSURANCE7/2/202445,4138
BANCASSURANCE7/2/202445,4138
BANCASSURANCE7/2/202450,9314
BANCASSURANCE7/2/202445,4138
BANCASSURANCE7/2/202450,9314
BANCASSURANCE8/2/202450,9314
MULTIAGENTS13/2/202422,8
BROKERS1/3/202414,44
BROKERS1/3/2024439,4966
BROKERS14/3/202474,8182
AGENTS14/3/202414,44
AGENTS14/3/202414,44
AGENTS14/3/20241900
AGENTS14/3/2024228,5016
AGENTS15/3/2024456
AGENTS19/3/202422,8
AGENTS19/3/202414,44
MULTIAGENTS20/3/2024320,682
AGENTS20/3/2024303,9658
AGENTS21/3/202495,7258
AGENTS22/3/2024264,5446
MULTIAGENTS23/3/2024180,9674
AGENTS26/3/2024265,1146
AGENTS26/3/2024177,7792
MULTIAGENTS12/5/2023430,3386
BROKERS23/1/2023532,6916
BROKERS31/3/2023186,27
MULTIAGENTS28/6/2023476,8088
MULTIAGENTS28/6/2023611,8988
MULTIAGENTS30/10/2023701,9056
MULTIAGENTS16/10/2023321,4572
MULTIAGENTS28/3/2023395,6142
MULTIAGENTS17/2/2023232,7462
MULTIAGENTS10/7/2023395,6104
MULTIAGENTS28/11/2023695,2138
MULTIAGENTS20/6/2023161,79
MULTIAGENTS3/1/2023510,7504
MULTIAGENTS21/10/202334,96
MULTIAGENTS21/10/202334,96
MULTIAGENTS21/10/202334,96
BROKERS2/2/20231923,716
BROKERS9/1/2023699,52
BROKERS8/3/2023731,25
BROKERS9/5/2023238,1536
BROKERS13/7/2023731,23
BROKERS30/6/202365,4664
AGENTS21/7/2023153,2008
MULTIAGENTS21/7/2023153,1666
MULTIAGENTS8/9/2023230,4852
AGENTS28/4/2023132,0462
AGENTS19/9/2023341,0196
AGENTS4/8/2023205,2456
AGENTS25/8/2023379,9848
AGENTS15/2/2023261,6604
AGENTS20/2/202371,6034
AGENTS17/2/2023464,2498
AGENTS15/2/2023130,8758
AGENTS16/1/2023168,0968
AGENTS26/7/202371,6034
AGENTS1/12/2023306,0672
AGENTS20/12/202376,304
AGENTS3/4/2023134,4744
AGENTS21/3/2023213,104
AGENTS16/3/2023119,662
AGENTS28/3/2023259,7642
AGENTS16/3/2023123,3062
AGENTS6/7/2023193,6594
AGENTS14/4/2023309,7266
AGENTS25/12/2023456
AGENTS21/3/2023143,07
AGENTS29/3/2023719,2108
AGENTS27/6/20231264,192
AGENTS27/9/2023147,4248
AGENTS14/9/2023201,685

Hi,

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

Hope this helps.

Ashish_Mathur_0-1712577813654.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Not really but thanks anyway Ashish

Regards

 

lbendlin_0-1712597030219.png

 

you can refine it as needed.

 

Thank you so much

 

Regards

lbendlin
Super User
Super User

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...

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.