Forum Discussion
Year over Year with selected date range
Folks,
I’ve been scouring these forums and other places and I haven’t been able to find a solution for something my business users want.
As you know, comparing year over year data is easy. Current fiscal year, previous fiscal year…cake.
However, what my users want is to select a date range from a slicer when they're in the report and then see that selected date range as “current year” and they want to see the same range of dates, but one year earlier as “previous year”.
That means that when I pull the data in from SQL, I don’t know if any give piece of data will be “current year”, “previous year” or outside the selected ranges completely.
The closest I’ve been able to come…and it’s an ugly kludge, is to require them to put a start date no earlier than 1 year before today. Then as I load the data, anything on or after that arbitrary date is “this year” and anything earlier is “last year”. Of course, I have to make sure not to load any data before 2 years ago.
Anyone know a more elegant way to do this?
Thanks!
Dave
Anonymous , You can try a trailing year measure with date table, Slicer should be used from date table
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
you can also check SAMEPERIODLASTYEAR
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
3 Replies
- amitchandak
Super User
Anonymous , You can try a trailing year measure with date table, Slicer should be used from date table
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
you can also check SAMEPERIODLASTYEAR
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.