Forum Discussion
SamePeriodLastYear
I have been using the samperiodlast year function to calucation prior year revenues and counts of jobs and quotes. For some reason it has stopped working for me.
If I use this calculation then the number is the same as the current year no matter what I do. If change the date to be the job start date which I have linked to the calendar date table then it turns out blank. I am getting so frustrated and I have been doing these for a while and it is probably something small and stupid I am missing.
12 Replies
Check your calendar/date table to make sure it includes 2020 dates.
Second thing...your formulas seem like they could take advantage of time intelligence. For example:Current Year Estimated Revenue = TOTALYTD( [Total Estimated Revenue] Calendar(Date) ) Prior Year Estimated Revenue = TOTALYTD( [Total Estimated Revenue] SAMEPERIODLASTYEAR(Calendar(Date)) )I don't know the name of your date table so I'm improvising. But try checking those two things...
So my calendar tables goes from 2017 to 2021. Also, I am not calculating for a year. I have chiclets that have the year and month so that the user can determine the time frame the want to look at.
Total Est Rev = sum('Quotes Estimates US'[Estimate Revenue])Total Est Rev PY = CALCULATE([Total Est Rev],SAMEPERIODLASTYEAR('Calendar'[Date]))See the below snap shot of the page. I also have My Quotes table linked to the Calendar table via the jobsdate. Which is where the month and year are being pulled from.Try
Total Est Rev PY = CALCULATE([Total Est Rev],ALL('Calendar'),SAMEPERIODLASTYEAR('Calendar'[Date]))
If im not misstaken you have a doublesided crossfilter relationship between your calendar and your facts table. This should be the reason that your measure isn't working without the ALL() statement.
Br,
J
