cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Same Period Last Year

The dax function Same Period Last Year DAX is not giving the actual dates shifted by 1 Year back.

This calculates till the end of the month previous year and not to the same day last year.

LASTDATE(Dates[Date)) gives me 2020/7/26, but LASTDATE(SAMEPERIODLASTYEAR(Dates[Date])) gives me 2019/7/31, which is not the same period.

 

This is a serious bug which makes the calculations wrong

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

@SubinPlus
1) Do you have Dates table marked as Date table? https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
2) What context are you viewing these results in? SAMEPERIODLASTYEAR will work in the current context of the report and I believe it also only goes to month granularity, not day as DATEADD will provide, so if you are looking at month of July, it will encompass all dates for July.
3) Try DATEADD(Dates[Date], -365, Day) and that might give closer result to what you're expecting? Note this is slightly different than DATEADD(Dates[Date], -1, Year). Try them both in your report side by side to see the difference.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Super User IV
Super User IV

Hi @SubinPius 

 

try this.

SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Hi @SubinPius ,

 

what value do you expect for the period?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Community Champion
Community Champion

@SubinPius 

You are supposed to get the same date less one year. Both DATEADD works the same way.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Highlighted

I expect the end date of same period last year dax to be 26/7/2019. Today is 26/7 2020, so the same period last year should be from 1/1/2019 til 26/7/2019. But i get dates from 1/1/2019 till 31/7/2019 which is wrong.

Please find below the scrrenshots for better understanding

 

Below is the last date from the calendar table.

snapshot1.PNG

 

Below is the last date for the same period last year which gives 7/31/2019. Ideally it should be 7/26/2019.

SubinPius_0-1595756614174.png

 

Highlighted
Super User II
Super User II

@SubinPlus
1) Do you have Dates table marked as Date table? https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
2) What context are you viewing these results in? SAMEPERIODLASTYEAR will work in the current context of the report and I believe it also only goes to month granularity, not day as DATEADD will provide, so if you are looking at month of July, it will encompass all dates for July.
3) Try DATEADD(Dates[Date], -365, Day) and that might give closer result to what you're expecting? Note this is slightly different than DATEADD(Dates[Date], -1, Year). Try them both in your report side by side to see the difference.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted

Hi Allison,

Date table is already marked a date table.

The report doesn't have any filters applied and calendar table contains dates from 1/1/2019 to 26/7/2020.

I was in the impression that SAMEPERIODLASTYEAR gives day level granularity and not month level.

This behaviour doesn't give much value in terms of comparison as this is not accurate if you are in middle of any month.

I will try as per your suggestion. Thank a lot.

Highlighted
Community Champion
Community Champion

@SubinPius 

You must have DATES for the full year, make sure your dates are until 31 DEC 2020

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Highlighted
Super User IV
Super User IV

Hi @SubinPius 

 

try this.

SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

View solution in original post

Highlighted

HI @SubinPius,

I'd like to suggest you try to use the date function to manly calculate out the date range what you wanted.

It should more agility than time intelligence functions and it supports a few advanced operations. (e.g. nested, filter with specific rules based on current value and calculations)

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors