• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dateadd vs. Sameperiodlastyear

Shay A

Member
Hi,
What's the advantage of using one over the other? I understand that Dateadd offers more flexibility but lets say I am only interested in last year ago. Not anything before that. What is preferred?

TY
 
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.
 
Last edited:
Back
Top