Help With Cumulative Line Chart Showing Year Over Year Sales Growth

[meganm1996]

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!

@AlexisOlson 

[meganm1996]

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 · Accepted solution]

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]

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? 

[AlexisOlson]

Projecting/forecasting is a fair bit of extra work unless you use the built-in feature.

You can use the relative date filtering on the chart to eliminate future dates.

[AlexisOlson]

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]

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]

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.