cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BeatNick Frequent Visitor
Frequent Visitor

SAMEPERIODLASTYEAR() Sums entire previous year

I'm trying to follow the standard sales formulas of YTD, and YTD for Prior year. Here are my formulas below:

 

SalesTotal = SUM('FactOrderDetails'[SalesAmt])

SalesTotalYTD = TOTALYTD([SalesTotal], DimDate[Date])

SalesTotalPreviousYTD = CALCULATE([SalesTotalYTD], SAMEPERIODLASTYEAR(DimDate[Date]))


The problem is that SalesTotalPreviousYTD forumla sums the entirety of last years sales, not the expected YTD. I initally suspected it was my Year slicer I had on the report but I removed it and the totals are still incorrect. I've tried a variety of other forumlas from around the web but they all seem to sum the entire previous year and this in theory should be fairly straight forward. 

Are there any other areas I can spot check to make the forumla sum the data as expected? Follow up question, can I modify the forumlas above to play nice with a YEAR slicer on the report.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SAMEPERIODLASTYEAR() Sums entire previous year

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: SAMEPERIODLASTYEAR() Sums entire previous year

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


BeatNick Frequent Visitor
Frequent Visitor

Re: SAMEPERIODLASTYEAR() Sums entire previous year

Thanks for the link Greg! I was able to get something working with the following code:

 

SalesTotalPreviousYTD = 
VAR __MaxYear = MAX('DimDate'[Year])
VAR __MaxMonth = MONTH(TODAY())
VAR __TmpTable = CALCULATETABLE( DimDate ,ALL('DimDate'[Year]),All('DimDate'[Month]))
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear-1 && [Month] <= __MaxMonth),[SalesTotal])

While I'm happy I can start moving forward on my project this code is much harder to quickly read and might be difficult for anyone new to Power BI. Can you provide any insight on why I'm unable to use the built in functions to avoid this messy code? My DimDate table is marked as a date table, the TOTALYTD() function works as designed... I figure last years sales should be a just as easy.