SamePeriod Last Year is calculating -365 not -364

[Tvisual]

Hi, 

I have upgraded my Power BI Desktop to new release (Version: 2.68.5432.841 64-bit (April, 2019)). For some reason my same period last year is not working properly. I have downloaded 64 bit. 

So basically, I created a date table in Excel and imported into powerbI desktop, applied "Marked as date table". Created a function (Same period last year) and for 04/28/2019 it is giving me 04/28/2018 not 04/27/2018. 

Do you know what I am missing? 

T

[edhans]

That is calculating correctly. It isn't doing the date minus 365. It is doing the same period last year. If your context is at the month or quarter level, it will report the same month or quarter lest year. So for April 2019, SAMEPERIODLASTYEAR() will return all dates for April 2018, not March.

The only weirdness is with leap years at the day level granularity.

It will report Feb 28 as the SPLY for Feb 29.

See the definition for more info. If you want it to be 364 days back, consider the DATEADD() function. 

Be careful with how you are using SAMEPERIODLASTYEAR. It doesn't return a date. It returns a table of dates. At the day level, it happens to be one date. At the month level, it will be 28-31 records. That can cause a measure to return an error if you aren't handling it as a table to start with.

[Tvisual]

Is there a way to get -364 value as compared to -365 and adjust with leap year?

[v-juanli-msft · Accepted solution]

Hi @Tvisual 

Per your reuquirement, create a measure

LY you expected = CALCULATE(MAX('calendar'[Date]),DATEADD('calendar'[Date],-366,DAY))

As tested, for 2/29 in a leap year, use "SAMEPERIODLASTYEAR' will get a duplicate date at this date, but use "dateadd" can have different dates.

There are measures as below, select the one most satisfying your requirement.

LY = CALCULATE(MAX('calendar'[Date]),SAMEPERIODLASTYEAR('calendar'[Date]))

LY you expected = CALCULATE(MAX('calendar'[Date]),DATEADD('calendar'[Date],-366,DAY))

LY2 = CALCULATE(MAX('calendar'[Date]),DATEADD('calendar'[Date],-365,DAY))

On my site, 2016 is a leap year.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Yes. You'd need to use the DATEADD() function and use 364 unless 2/29 is a valid date for the current year, then use 365.