Forum Discussion
Card - Cumulative YTD % Difference
Hi all,
Haven't been able to find a solution for this specific request:
I have a table that gets updated daily with sales figures, and I would like to have a simple Card that shows the % difference in sales between the current/latest date in the table, against the same date from the previous year but in cumulative sales, not just for the latest date.
So if I'm currently in 24 Jan 2021, I would like a card that compares cumulative sales from 1 Jan 20 to 24 Jan 20, compared to 1 Jan 21 to 24 Jan 21.
Thanks.
Add the fiscal year to the DATESYTD measures:
DATESYTD( DimDate[Date], "09-30")
4 Replies
Anonymous
You can do this with a series of measures and a date table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Total Sales = SUM(Sales[Sales])
YTD Sales = CALCULATE( [Total Sales] , DATESYTD(DimDate[Date])
Prior YTD Sales = CALCULATE( [YTD Sales], DATEADD(DimDate[Date], -1, YEAR) )
% Diff YTD Sales vs Prior Year = DIVIDE( [YTD Sales] - [Prior YTD Sales], [Prior YTD Sales] )
Hi AllisonKennedy thanks for your quick response.
That was really helpful and I think I'm really close to a solution. I'm getting a value when I use the "% Diff YTD Sales vs Prior Year" measure on a Card, but it's not quite what I am expecting. Sorry but I forgot to mention in my original post that I need to look at these cumulative % differences by fiscal year. I do already have a date table that includes a fiscal year column which starts on October 1.
Do I have to change your measures above to include the fiscal year instead?
Thanks.
Add the fiscal year to the DATESYTD measures:
DATESYTD( DimDate[Date], "09-30")