Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GMS0101
Frequent Visitor

SAMEPERIODLASTYEAR only calculating cumulative amounts

Hello!

I've seen many forum pages and blogs of people using a combinatio of CALCULATE and SAMEPERIODLASTYEAR to calculate prior period revenue but my formulas to get prior period revenue return the cumulative sum, one period back. In this sample case, I'm trying to calculate prior year revenue.

 

My Measures, report, and table information are below:

Prior Period Sales = CALCULATE(sum(MyTable[Sales]),DATEADD(MyTable[Date],-1,YEAR))

Prior Period Sales 2 = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(MyTable[Date]))

2019-09-15_11-40-45.jpg

 

Table Info.jpg

 

 

I'm expecting to see the prior year's revenue on each line but I get blanks. The subtotal for each of the Prior year columns is a cumulative sum of all years up to the prior year. How do I get prior year revenue to show up on those blank spots?

 

Thank you to anyone who can help this PowerBI noob with his troubles.

 

Garrett

 

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @GMS0101 ,

You need to create a new calendar table and create a relationship between the two tables.

Table = CALENDARAUTO()

3.PNG

Prior Period Sales   = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR))
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

4.PNG

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.

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.

View solution in original post

6 REPLIES 6
v-xuding-msft
Community Support
Community Support

Hi @GMS0101 ,

You need to create a new calendar table and create a relationship between the two tables.

Table = CALENDARAUTO()

3.PNG

Prior Period Sales   = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR))
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

4.PNG

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.

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 

 

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]))))

3.PNG

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.

 

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

Hi @GMS0101 ,

Oh, I understand clearly this time. After testing, your formula will work when I use the date from calendar table. Do you try this? Did it work in your sample?

YTD SALES = TOTALYTD([Total Sales],'Table'[Date])
Measure = CALCULATE([YTD SALES], SAMEPERIODLASTYEAR(MyTable[Date]))

6.PNG

I attached my sample that you can download.

 

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.

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.

Those formulas work for me.

 

I just wanted to see if there was a way  to use the original combination of SUM() and SAMEPERIODLASTYEAR as I saw others saying it was possible so I just wanted to verify if that was the case or not.

 

Thanks for all your help Xue! Seriously appreciate all the help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.