Not only for sameperiodlastyear,other TI functions have same issues in different contexts .So i followed the offset method based on calender table.That is 100% accurate on different granular levels. Brian Julius and Greg deckler has published many articles,videos on this.
A side question. @6:55 your DAX measure start and end date is using the month and day functions. If your calendar table has the month and day numbers as fields already, could/should you use the MAX/MIN of those instead?
Completely understand your complain. The reason why I do it this way is because I had to change servers once before and all those links broke. I had like 500 links :(
I've never liked the sameperiod functions as they ignore days of the week. Why would you want to compare sales on a Monday to a Sunday? Also holiday dates differ.
Do you have a full data range in your calendar table? I mean all years with dates from 01-01 till 12-31? Because this is required for time intelligence calculations to work properly. Also waiting for the file to test it :)
PARALLELPERIOD(Date,-12,MONTH) is also another problematic function because it seems to not work when it's down to days granularity. Funnily, SAMEPERIODLASTYEAR was the fix. Anyway, thanks for this because might never know when it comes useful.
Try not to use date column from Fact table in Time Intelligent function, use date column from Dimension table (from seperate calendar table ) would probably solve this issue.
So is today really today and tomorrow really tomorrow once you publish to the service and the service and local datetime zone might be different to where the local user and their settings are :)
In my case, SPLY manually returns the sum of the whole year for each month. However, I see in this video the daily breakdown of the 15,6K. I dont understand why I dont get the same result :/
Not only for sameperiodlastyear,other TI functions have same issues in different contexts .So i followed the offset method based on calender table.That is 100% accurate on different granular levels. Brian Julius and Greg deckler has published many articles,videos on this.
A side question. @6:55 your DAX measure start and end date is using the month and day functions. If your calendar table has the month and day numbers as fields already, could/should you use the MAX/MIN of those instead?
Thanks very much :) It is helpful. I was not aware about this glitch. Thanks
How is the calendar made?
This is great. Thanks!
Do you think it is possible to add to the DAX calculation also the time?
Does the same thing happen for DATEADD ,
Most likely as SMEPERIODLASTYEAR is syntax sugar for DATEADD
I wish you would provide a link to this file download, not the entire site
Completely understand your complain. The reason why I do it this way is because I had to change servers once before and all those links broke. I had like 500 links :(
@@CurbalEN Thank you!!
Yesterday, Goodly released video exactly about this. What a synchro
😂😂 I released mine for members yesterday, so I am innocent! Was the solution the same too?
excellent, did not know about this! Thank you for sharing.
Did you make you date table properly? Meaning, does you date table have every date for all years present?
Yes :)
@@CurbalEN
God, now I have to go and check 50 reports with 30 measures... Why I chose PowerBI, why...
Actually, no, I will let my clients inform me about this...
😢 Sorryyyy
😂
how about PREVIOUSYEAR('Calendar'[Date])
It's also tricky to calculate SPLY when this year is leap year.
Leap years are always fun!!
I've never liked the sameperiod functions as they ignore days of the week. Why would you want to compare sales on a Monday to a Sunday? Also holiday dates differ.
Yeah, they should say same datel last year. A same day last year would be welcomed!
Hello Curbal,
Always interesting your tuorials
But how can I retrieve the.pbix file you used in the video?
Thanks
It is the northwind dataset, but others have asked so I will add it to the download center next week
@@CurbalEN
Thks so much
Do you have a full data range in your calendar table? I mean all years with dates from 01-01 till 12-31? Because this is required for time intelligence calculations to work properly. Also waiting for the file to test it :)
Sorry I forgot! As i mentioned, it works if you have a full calendar, but it is a common practice not to have it.
Sa iti dea al de sus sanatate si multa fericire :* 100% working
Thank you! i will use DAX :)
PARALLELPERIOD(Date,-12,MONTH) is also another problematic function because it seems to not work when it's down to days granularity. Funnily, SAMEPERIODLASTYEAR was the fix.
Anyway, thanks for this because might never know when it comes useful.
Thx❤
Agree. They have to fix this.
This behavior described in defenitive guide to dax book.
It should be in the docs though…
Try not to use date column from Fact table in Time Intelligent function, use date column from Dimension table (from seperate calendar table ) would probably solve this issue.
I did that :)
So is today really today and tomorrow really tomorrow once you publish to the service and the service and local datetime zone might be different to where the local user and their settings are :)
Yeah, why make it easy right? 😂
Horrible, but a great solution to the problem 👌
In my case, SPLY manually returns the sum of the whole year for each month. However, I see in this video the daily breakdown of the 15,6K. I dont understand why I dont get the same result :/