cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanessafvg
Super User
Super User

SAMEPERIODLASTYEAR not working as expected

I have this  calcualted measure, but it doesn't bring anything back - i can see data for last year so its a bit odd

PY Revenue = CALCULATE(sum(Sales[RevenueNettAmount]), SAMEPERIODLASTYEAR('Fiscal Date'[Date]))

 

Im obviously missing a link somewhere, any tips?

 

I have a filter Fiscal Month and Year, and then i am placing my date field on the graph in the right hand side where i was expecting to see the trend of last year too.   

Capture.PNG

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

My guess on this is that you are filtering Fiscal Date[Date] through your slicers and so when you feed it to SAMEPERIODLASTYEAR in a measure, it is filtered to a single month/year and thus all of last year's information is not there. I would try wrapping an ALL around 'Fiscal Date'[Date]) to remove any slicer filter context.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

4 REPLIES 4
nandic
Super User
Super User

Try this approach: mark you date dimension as date dimension (screenshot below).

 

Mark as date table 2.PNG

 

After having this, you use date column from this dimension to get same period last year.

Example: 

Total Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Table 'Date' is marked as Date dimension.
 
Result is list below:
Same Period Last Year list.PNG
Cheers,
Nemanja
 
Greg_Deckler
Super User
Super User

My guess on this is that you are filtering Fiscal Date[Date] through your slicers and so when you feed it to SAMEPERIODLASTYEAR in a measure, it is filtered to a single month/year and thus all of last year's information is not there. I would try wrapping an ALL around 'Fiscal Date'[Date]) to remove any slicer filter context.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

that definitely seems to be what the issue is, now if i could just get it to plot correctly on my line graph!  

 

thanks





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




From what I have observed if you do the following it does work:

 

1. The DATE table should have unique rows which contain ALL the dates from your dataset. So if your FACTS table cotains daily transactions from 2010 to 2017 with a few missing days the DATE table should contain those missing days as well.

2. Connect your DATE table with the FACTS table with the DATE key(ex: 01/03/2017) and not a DATID key

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.