Forum Discussion
SAMEPERIODLASTYEAR() function
Hi All,
I'm just start learning Power BI, but I couldn't get my head around on the SAMEPERIODLASTYEAR() function.
Assume I have a table with two columns date and sale, the date ranges from 1/1/2001 to 12/31/2005. From my understanding, if I do SAMEPERIODLASTYEAR() on the date column, I'll get a column with date from 1/31/2000 to 12/31/2004.
If I create a measure, test_measure = calculate(sum(sale), sameperiodlastyear(date)), does this compute sum of sale from 1/31/2000 to 12/31/2004?
But the MSDN page says this.
The following sample formula creates a measure that calculates the previous year sales of the Reseller sales.
=CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))
My understanding is the measure above computes the sum of sale from oldest date to the (latest date - 1 year), Could someone points out what's wrong with my thought?
Thanks!
Your formula works fine. You are just not filtering it correctly. Meaning you need to filter/select the current period you want to measure.
Try this:
1. Create a Total Sales measure: Total Sales =
Total Sales= SUM(ResellerSales_USD[SalesAmount_USD])
2. Create a Previous Year Total Sales =
Previous Year Total Sales=CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTime[DateKey])
3. (Drag your date field into the worksheet and make it into a slicer (use slicer visualization)
4. Drag your Total Sales and Previous Total Sales fields into the worksheet as well and make them into a card visualization (It is just easier this way, or you can make it into a table visualization)
5. Now select any month in your date slicer. Pick 3/1/2017. You will see that the Total Sales card shows 27 and the Pevious Year Sales card shows 15. You can even select 3/1, 2/1 and 1/1/2017 and it will show you previous year total Jan-March.
So you can see it is working, you need to be able to filter it to your liking. Hopefully this helps you get started. The next steps you will need is to create a measure calculation that incorporates a filter such as TOTALYTD.
9 Replies
Hi higherkuo
Do you have a Date or Calendar table in your model?
Some of the DAX functions like SAMEPERIODLASTYEAR will work better with a date/calender table.
Hi Phil_Seamark
my table has one column of date, I create something very simple to test my thought.
If I perform calculate(sum(sales), sameperiodlastyear(date)) this measure, it returns the sum from 1/1/2015 ~ 3/1/2016. (inclusive), which is 120 (1+2+3...15).
Bu I just couldn't understand why MSDN says
=CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))
"The following sample formula creates a measure that calculates the previous year sales of the Reseller sales."
Should it be?
"The following sample formula creates a measure that calculates the all sales except the most rescent year sales of the Reseller sales."
Thanks!
Your formula works fine. You are just not filtering it correctly. Meaning you need to filter/select the current period you want to measure.
Try this:
1. Create a Total Sales measure: Total Sales =
Total Sales= SUM(ResellerSales_USD[SalesAmount_USD])
2. Create a Previous Year Total Sales =
Previous Year Total Sales=CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTime[DateKey])
3. (Drag your date field into the worksheet and make it into a slicer (use slicer visualization)
4. Drag your Total Sales and Previous Total Sales fields into the worksheet as well and make them into a card visualization (It is just easier this way, or you can make it into a table visualization)
5. Now select any month in your date slicer. Pick 3/1/2017. You will see that the Total Sales card shows 27 and the Pevious Year Sales card shows 15. You can even select 3/1, 2/1 and 1/1/2017 and it will show you previous year total Jan-March.
So you can see it is working, you need to be able to filter it to your liking. Hopefully this helps you get started. The next steps you will need is to create a measure calculation that incorporates a filter such as TOTALYTD.
