Forum Discussion
Shifting current calendar year dates back one year issues
Hello Power BI community,
My team and I encountered some strange results using the dateadd and sameperiodlastyear functions to shift the current calendar year dates back one year. We have a typical date table that includes dates from January 2019 up until the current calendar date. To grab the current period Dates I used the following: CurrentDates = DATESYTD('Date'[Date]), which correctly produces the dates in the current calendar year (1/1/2021 - 7/23/2021). What's interesting is that when I try to shift those dates back one year using: CurrentDatesLY = DATEADD(CurrentDates, -1, YEAR), it includes through 7/31/2021:
When I use DATEADD(CurrentDates, -365, DAY) it returns the correct range:
Does anyone have any ideas why this happens? I also used SAMEPERIODLASTYEAR(CurrentDates) and it produced the same issue including to 7/31/2021. If anyone knows what's happening that would be much appreciated, I am currently on version 2.95.804.0.
3 Replies
- vanessafvg
Community Champion
I think Marco Russo potentially explains it better than I. I think this might be the issue around the function you are using.
CNENFRNLThe narrator in the referred clip is Alberto Ferrari😀
As pointed out at 3:10 of the clip, the intervention of the invisible ALL() on date column is the crux of unexpected behaviour of TOTALYTD/DATESYTD. Good to see it unveiled!
Cheers!
- vanessafvg
haha CNENFRNL freudian error....you would think i would know that seeing as i have watched more videos from them over the years than i have tv! however they pretty synonomous with each other in this world 😉
