Forum Discussion
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.
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.
5 Replies
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.
that definitely seems to be what the issue is, now if i could just get it to plot correctly on my line graph!
thanks
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
Try this approach: mark you date dimension as date dimension (screenshot below).
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:Cheers,Nemanja
