Forum Discussion
Help With Cumulative Line Chart Showing Year Over Year Sales Growth
- 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] ) ) )
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:
- AlexisOlson2 years ago
Super User
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] ) ) )- meganm19962 years agoNew Member
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?
- AlexisOlson2 years ago
Super User
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.