SamePeriodLastYear

[reh169]

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.

Total Est Rev PY = CALCULATE([Total Est Rev],SAMEPERIODLASTYEAR('Calendar'[Date]))

[littlemojopuppy]

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... 

[reh169]

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.

[tex628]

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

[reh169]

That is still resulting in the amounts being the same. I even changed the year to double check that I have plenty of data for 10/18 and I do. This is what my relationship between the quotes table and the calendar looks like. Any other thoughts? What is messed up is that in my jobs table I am doing the exact same thing with est and act revenue and it is working fine.

[tex628]

Try changing the format of the 'Calendar'[Date] column to Date isntead of DateTime. Looks like there might be an issue with the dataformatting.

/ J

[reh169]

I did that and it is still giving me blank as my results for the prior year. I am doing the exact same thing I have done before I just go not understand what the issue is.

[tex628]

This is just really strange.... What are you getting if you just do the following:

Calculate(
Max( 'Calendar'[Date] ) ;
Sameperiodlastyear( 'Calendar'[Date] )
)

or

Calculate(
Max( 'Table'[JobbSDate ) ;
Sameperiodlastyear( 'Calendar'[Date] )
)

I can't really see any remaining reason as to why this isn't working with the information provided. 

If you could share the PBIX it would help but you need to make sure that the file does not contain any confidential information. 

You can then share it by Google Docs or some similar filesharing service. 

[reh169]

So the first one works and gives me the last date in my calendar table. The second one gives me the last day of the month for what I have selected. It is not giving me the prior year, it is showing me the same year that I am filtering for. I have been trying to create a work around to just give me the prior year and then use that in my calculation but that is not working either.  For the calcuation below whether I am using the jobsdate for the date field from the calendar table I get the same error message.

PY = Calculate(Sameperiodlastyear( 'Quotes Estimates US'[JobSDate] ))

I will try and attach the pbix file.

[tex628]

Are the year and month slicers from your facts table and not your calendar table? If so try swapping them to year/month columns in your calendar table!

/ J

[reh169]

That did it although I do not understand why making that change made the difference.

[littlemojopuppy]

Can you share the PBIX file?

[reh169]

How do I do that safely?