Dateadd vs. Sameperiodlastyear
- Thread starter Shay A
- Start date
Chihiro
Excel Ninja
It's really personal preference. If you are concerned only with last year. SAMEPERIODLASTYEAR may be better, since it's easier for other's to understand what the formula is supposed to do.
There are few things you should note for both these functions.
Both functions apply special logic when shifting the last day and uses last day of the month (not just up to current day of month context).
EDIT: Above is true if you use month for DATEADD. What date is used as last day will depend on the argument of interval.
It is because of these type of behavior of time intelligence functions, you should always create date dimension table with full year's worth of dates (not just up to current date).
For partial month comparison, you should use some other formula construct (such as Filter(Table/Column,[StartDate]<=[Dates] && [EndDate]>=[Dates]))
As well if you are using PowerBI, both functions aren't supported in Direct Query mode. You'd need to use one of supported DAX formula to construct your measure.
https://docs.microsoft.com/en-us/sq...a-compatibility-in-directquery-mode-ssas-2016
EDIT: Where these functions shine is when you are comparing uneven month (i.e. Feb) for leap year and regular year.
There are few things you should note for both these functions.
Both functions apply special logic when shifting the last day and uses last day of the month (not just up to current day of month context).
EDIT: Above is true if you use month for DATEADD. What date is used as last day will depend on the argument of interval.
It is because of these type of behavior of time intelligence functions, you should always create date dimension table with full year's worth of dates (not just up to current date).
For partial month comparison, you should use some other formula construct (such as Filter(Table/Column,[StartDate]<=[Dates] && [EndDate]>=[Dates]))
As well if you are using PowerBI, both functions aren't supported in Direct Query mode. You'd need to use one of supported DAX formula to construct your measure.
https://docs.microsoft.com/en-us/sq...a-compatibility-in-directquery-mode-ssas-2016
EDIT: Where these functions shine is when you are comparing uneven month (i.e. Feb) for leap year and regular year.
Last edited: