Forum Discussion
SAMEPERIODLASTYEAR only calculating cumulative amounts
Hi GMS0101 ,
You need to create a new calendar table and create a relationship between the two tables.
Table = CALENDARAUTO()
Prior Period Sales = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR)) Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))Note:
- The date in tables is from calendar table.
- Due to we calculated the sales of previous year, it only can be shown as year and entire date, like the pic above. We can't drill down to quarter , month and day.
I attached my sample that you can reference.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi GMS0101 ,
You need to create a new calendar table and create a relationship between the two tables.
Table = CALENDARAUTO()
Prior Period Sales = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR))
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))
Note:
- The date in tables is from calendar table.
- Due to we calculated the sales of previous year, it only can be shown as year and entire date, like the pic above. We can't drill down to quarter , month and day.
I attached my sample that you can reference.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for responding Xue! This makes sense.
One last question:
Let's say I wanted to put the same measure for total sales in prior year into a card visual and let's use this measure:
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))How would I get the card visual to show PY revenue instead of cumulative? Or would I use a different measure to do this? As far as using the card, I can accomplish this using TOTALYTD but not sure if that was the intended way of doing this.
Thank you soooooooooo much!
-Garrett
- v-xuding-msft
Hi GMS0101 ，
How do your measure of Total Sales like? Can you share it?
I created a new formula.
Calculate = CALCULATE(SUM(MyTable[Sales]),FILTER(ALL(MyTable[Date]),MyTable[Date] <=MAX(MyTable[Date])&&YEAR(MyTable[Date])=YEAR(MAX(MyTable[Date]))))
Is this what you want? If not, please share your expected output.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Xue,
My total sales meausre is being calculated as: Total Sales = SUM(MyTable[Sales])
Using my data in the test file in my original post, I am expecting that if I put the new Prior Year Sales measure that you provided into a card, that it would show 75 (the revenue in 2018). Instead, it shows the cumulative amount up to prior year in the card; which is 225. Is there a way to get this to show the 2018 amount without using a filter?
So far, I've figured out how to do this using the following measures but not sure if this is the right way to do this:
YTD SALES = TOTALYTD([Total Sales],'Date'[Date])PY YTD SALES = CALCULATE([YTD SALES], SAMEPERIODLASTYEAR('Date Table'[Date]))The measure, PY YTD SALES, will calculate 75 when I put it into a card visual which is what I'm looking for with the previous measure.
-Garrett
Thank you
