Forum Discussion
Percentage difference Previous Year - No dates
Hi All,
I'm trying to create measures to calculate year on year % difference. (should be simple but its Friday :smileyhappy:)
As it stands my data does not have dates, just a year identifier i.e. "14/15","15/16"
I have a lookup table for the year identifier with a sort order column that has an integer number for each year so I can sort correctly. I was hoping to just use the sort order column to identify previous year ([sort_order] - 1) but running into trouble.
Is it possible to use calculate to sum values for previous year identifier? I'm not sure the time inteligence fiunctions will work for me as my data doesn't have real dates and I don't really want to have to create spurious ones.
Got there in the end (funny how typing something out gets you to think)
Changed the formula to:
Calculate(SUM(Profile[I_FTE]),previousyear('Calendar'[End Date],"August 31"),all('Calendar'))and fixed the link that broke when I reimported my lookup table and hey presto
2 Replies
I have now added start date and end date fields to my lookup so there is a notional real date attached to my data, however the measure is blank when retrned in a matrix.
Code used is:
Calculate(SUM(Profile[I_FTE]),previousyear('Calendar'[End Date],"August 31"))
'Calendar'[End Date] is a date/time field
SUM(Profile[I_FTE]) works on its own
Any ideas?
Got there in the end (funny how typing something out gets you to think)
Changed the formula to:
Calculate(SUM(Profile[I_FTE]),previousyear('Calendar'[End Date],"August 31"),all('Calendar'))and fixed the link that broke when I reimported my lookup table and hey presto