Forum Discussion

meganm1996's avatar
meganm1996
New Member
2 years ago
Solved

Help With Cumulative Line Chart Showing Year Over Year Sales Growth

Hoping to get some help with an issue I'm running into trying to create a cumulative line chart for a sales report I'm working on. I'm trying to create a cumulative line chart to compare year over year sales between 2023 and 2024. I've followed several tutorials online and the closest I've gotten is shown below but it's essentially a flat line from 2020 to 2025. (I have not even tried to create 2 lines to compare 2023 vs 2024 yet. I'm still trying to get past the issue of my data showing up as a stagnant line). 

 

So far, I've done the following to try to get this to work. Any suggestions? 

1. Created a calendar table to compare the dates and built a relationship between it and my "deals list" data. 

 

2. Created a new measure for cumulative sales: 

Amount Cuml =
CALCULATE(
    SUM('deal list'[Deal - Value] ) ,
    'Calendar'[Date] <= MAX( 'Calendar'[Date] )
)
 
Do you have any suggestions for what else to try? I'd greatly appreciate any insights you may have!
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    OK, now that's a measure problem. You need a YTD cumulative measure.

     

    The shortest solution is probably

    TOTALYTD (  SUM ( 'deal list'[Deal - Value] ), 'DateTable'[Date] )

    but I'm not a fan of built-in time-intelligence functions.

     

    There are lots of ways of writing a TOTALYTD without time-intelligence. For example, a pattern like this one here:

    Month-related calculations – DAX Patterns

    Or you can use the newer WINDOW function and write something like this:

    Amount YTD =
CALCULATE (
    SUM ( 'deal list'[Deal - Value] ),
    WINDOW (
        1, ABS,
        0, REL,
        ORDERBY ( 'DateTable'[Date], ASC ),
        PARTITIONBY ( 'DateTable'[Year] )
    )
)

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    It's hard to tell what's going wrong here. I'm not sure why you have two calendar/dates tables and I can't tell what column in the deal table the dates are related to. 

    • meganm1996's avatar
      meganm1996
      New Member
      2 years ago

      The Calendar table is from another tutorial I watched. The "DateTable" is the original one created for this report. Dates are related to the Won Time Date in the Deals table. Do you recommend I remove the Calendar table to start? 

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        2 years ago

        I'd recommend using one or the other (not both) unless you have a specifc use case needing both.

         

        Dates are related to the Won Time Date in the Deals table.

        This might be your issue. If you are trying to connect a date column to a datetime column, you won't get many matches. Make sure both columns are date type for the relationship to function as expected.

  • meganm1996's avatar
    meganm1996
    New Member
    2 years ago

    I'm getting closer to what I need. I completely removed the Calendar table and am only going off of my Date Table, which seemed to be skewing it as you said. However, now it seems that the cumulative measure is pulling the cumulative amount of ALL sales from 2019-2024 and combining it instead of only showing me 2023 and 2024's cumulative amounts. 

    This is my cumulative measure: 

    Amount Cuml =
    CALCULATE(
        SUM('deal list'[Deal - Value] ) ,
        'DateTable'[Date] <= MAX( DateTable[Date])
    )
    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      2 years ago

      OK, now that's a measure problem. You need a YTD cumulative measure.

       

      The shortest solution is probably

      TOTALYTD (  SUM ( 'deal list'[Deal - Value] ), 'DateTable'[Date] )

      but I'm not a fan of built-in time-intelligence functions.

       

      There are lots of ways of writing a TOTALYTD without time-intelligence. For example, a pattern like this one here:

      Month-related calculations – DAX Patterns

      Or you can use the newer WINDOW function and write something like this:

      Amount YTD =
CALCULATE (
    SUM ( 'deal list'[Deal - Value] ),
    WINDOW (
        1, ABS,
        0, REL,
        ORDERBY ( 'DateTable'[Date], ASC ),
        PARTITIONBY ( 'DateTable'[Year] )
    )
)
      • meganm1996's avatar
        meganm1996
        New Member
        2 years ago

        That was exactly what I needed. Thanks for your help! 

         

        One other quick question in case it's requested when I submit this for review -- is there any way to add a projected amount for the future months or stop the 2024 line from reporting the future?