Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dkpmfa
Helper II
Helper II

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@dkpmfa , 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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@dkpmfa , 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.

I'll give that a try!  Thanks!  I'll let you know if it works.

It works!  Thanks a lot!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.