Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I had the strangest phenomenon happen today. A previously working YTD and previous year to date calculation just stopped working today.
This is the formula that I am using:
2019 YTD Members = TOTALYTD( SUM(MembersPerDate[Name]), MembersPerDate[Date], "06/30")
Previous FYTD = CALCULATE([2019 YTD Members], SAMEPERIODLASTYEAR(MembersPerDate[Date]))
Solved! Go to Solution.
Update:
I have figured out the reason I am having this issue is that SAMEPERIODLASTYEAR does not work how you would think it would.
For example, I have a date column that ends on September 5, 2019. So, I expect that when I call SAMEPERIODLASTYEAR(DateColumn) I should get a datetable that ends on September 5, 2018. Unfortunately, the table that I am getting returned returns table that ends on September 30, 2018.
Quite frankly, that is not intuitive from what the documentation for the formula is. The documentation does not state this is expected behavior and does not leave any hints as to why this would be.
Anways, I got the accepted result by doing the following
Previous YTD = CALCULATE([YTD SALES] , DATEADD(DateTable[Date], -365, DAY))
I know this will have issues with Leap Years but my information is not that date sensitive.
Update:
I have figured out the reason I am having this issue is that SAMEPERIODLASTYEAR does not work how you would think it would.
For example, I have a date column that ends on September 5, 2019. So, I expect that when I call SAMEPERIODLASTYEAR(DateColumn) I should get a datetable that ends on September 5, 2018. Unfortunately, the table that I am getting returned returns table that ends on September 30, 2018.
Quite frankly, that is not intuitive from what the documentation for the formula is. The documentation does not state this is expected behavior and does not leave any hints as to why this would be.
Anways, I got the accepted result by doing the following
Previous YTD = CALCULATE([YTD SALES] , DATEADD(DateTable[Date], -365, DAY))
I know this will have issues with Leap Years but my information is not that date sensitive.
Hi @Anonymous ,
Check your calendar table to find if the date is continuous. Would you like to share some sample data with us? This allows us to find the problem more accurately.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your response.
This is how my data is:
here is a link to the dataset: https://pikes-my.sharepoint.com/:x:/g/personal/mriedl_pikes_org/ET--4zOk0MlDmwXJ7a2gG8UB5V2Pf5sy36JM... ---FYI the link expires in a week.
Bsically, I want to get last years sales date from the start of my fiscal year 07/01 up until the current date. As I mentioned, the code snippet in the first post is working for another CSV/table that is in identical form as this one. Also, this code worked up until yesterday.
Thanks,
Michael
I should also note that I have an identical expression to calculate another YTD amount and it works perfectly. However, on today's refresh the above code failed to work.
So frustrating.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |