Forum Discussion
Compare cumulative data between years
Hi ilana105,
How do you set the Axis level, you have year and month field in your source data, you select the month as axis level, the year as legend level, right? If it is, you’d better add filter in measure to cumulative sum for each year, rather than all data. The TOTALYTD Function evaluates the year-to-date value of the expression in the current context. So it return the total sum for each year.
I try to reproduce your scenario as follows.
Create month and year calculated columns.Year = YEAR(Sales[DATE]) Year = YEAR(Sales[DATE])
Create measure using the formula below. The values function will return a table including one year.cumulative = CALCULATE(SUM(Sales[SALE]),FILTER(ALL(Sales),Sales[DATE]<=MAX(Sales[DATE])),VALUES(Sales[Year]))
Create the line chart, you will get expected result same to using TOTALYTD function like the following screenshot.TotalYTD = TOTALYTD(SUM(Sales[SALE]),Sales[DATE])
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
Hello BetterCallFrank
I have tried it but it is not working. Please find attached the formula and the graph
cumulativeImpressions =
CALCULATE (
SUM (database[impressions] );
FILTER(ALL(database);
database[finaldate] <= MAX(database[finaldate])
&& year( database[finaldate]) = year(MAX(database[finaldate]))
)
)
Thank you very much
Hi ilana105,
How do you set the Axis level, you have year and month field in your source data, you select the month as axis level, the year as legend level, right? If it is, you’d better add filter in measure to cumulative sum for each year, rather than all data. The TOTALYTD Function evaluates the year-to-date value of the expression in the current context. So it return the total sum for each year.
I try to reproduce your scenario as follows.
Create month and year calculated columns.
Year = YEAR(Sales[DATE]) Year = YEAR(Sales[DATE])
Create measure using the formula below. The values function will return a table including one year.
cumulative = CALCULATE(SUM(Sales[SALE]),FILTER(ALL(Sales),Sales[DATE]<=MAX(Sales[DATE])),VALUES(Sales[Year]))
Create the line chart, you will get expected result same to using TOTALYTD function like the following screenshot.
TotalYTD = TOTALYTD(SUM(Sales[SALE]),Sales[DATE])
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
This I have been looking for!!! All other answers gave me cumulative amounts year-to-year, not being able to compare year vs year.
Thank you!!!
Thanks Angelia. :smileyhappy:
Adding the
VALUES(Sales[Year])
part to the end of my calculate function worked. Awesome. I've been searching and trying for ages!
This has been really helpul but how do I make the graph be interactive by another column.
For example. If the sales in the graph are made by 5 sales people how can I show on the YTD trends for one of those sales persons.
It doesn't work. I am assuming it is something to do with the filter function?
Hello, Neilr,
Did you manage to solve the problem with interation to another columns? I have the same problem now.
How would you limit each year to not show data beyond the current date? In other words, the cumulative year to date value for each of the years, but equivalent to today's current date. That way you are "hiding" the future dates and only comparing dates that are relevant to the current year to date.
