Forum Discussion

ObsidianMagne's avatar
ObsidianMagne
Regular Visitor
2 years ago
Solved

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

 

 

 

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?

 

 

 

 

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

    • ObsidianMagne's avatar
      ObsidianMagne
      Regular Visitor
      2 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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's avatar
      ObsidianMagne
      Regular Visitor
      2 years ago

      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

       

      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