Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))
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
Solved! Go to Solution.
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:
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.
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:
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
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:
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]))
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.
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |