Forum Discussion
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
- @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. Hi Anonymous
try this.
SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))
8 Replies
- mwegener
Most Valuable Professional
Hi Anonymous ,
what value do you expect for the period?
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.
Below is the last date for the same period last year which gives 7/31/2019. Ideally it should be 7/26/2019.
- AllisonKennedy
Community Champion
@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.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.HI Anonymous,
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
- mwegener
Hi Anonymous
try this.
SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))
