SAMEPERIODLASTYEAR() function

[higherkuo]

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!

[Phil_Seamark]

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.  

[higherkuo]

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!

[Anonymous · Accepted solution]

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.

[higherkuo]

This makes way more sense to me.

Thank you!

[Sean]

http://exceleratorbi.com.au/dax-time-intelligence-beginners/

http://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[Anonymous]

Another tip, I highly recommend NOT USING A DATE TABLE.  The latest powerbi automatically creates a date table in the background, just make sure that the DATA TYPE of your date column is properly identified as a DATE in the Query Editor. 

The only time you need a date table now is if you have very specific attributes you want to include like Date zxy is "Summer"

[Phil_Seamark]

"Another tip, I highly recommend NOT USING A DATE TABLE. "

I have to disagree with you there and almost always use a date table except for the most simple and basic data models.

Apart from providing a central place to filter multiple fact tables, the groupings in the PBI default table are the bare minimum.  I always extend with useful columns like 

• Days from Today
• Weeks from Today
• Months from Today
• Custom Date Ranges
• Week Starting
• Fiscal year groupings
• Q&A optimised columns eg. "Today", " Current Week" , "Last Month" 
• etc
• etc

You can script these up in a single DAX statement, and I think there are a few floating around from other experienced posters on here .

There are also tricks you can do to guarantee the table you create is actually marked as a DATE table in the underlying Tabular cube.

[Anonymous]

Phil,

You are right. The only way I get DAX Date Functions to work (prior periods) is when using the Date table.

My twisted thinking is that calculations get confused when there is just something different about a date. The common table places multiple tables on the same date footing. 

[answeriver]

Hi all,

As i understand this function (SamePeriodLastYear) getting work that:

In data i have 2017 Full Year and 2018 before today. I want compare the same periods 2017 VS 2018, but function gives me sales for FULL 2017 year.