SamePeriodLastYear stopped working within YTD calculation

[Anonymous]

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]))

Up until today, I was getting the expected results.  However, today, the Previous FYTD is returning the values from 1/1/2018 - 9/3/2018 as opposed to the previous, and expected, output of 7/1/2018 - 9/3/2018.  I honestly did not change a single thing within either the application of the code, relationships, filters, etc.  II'm sure there is an underlying isssue with my code and logic but for whatever reason I can't seem to get it to work (even using DATEADD( date, -1, YEAR).

Does anyone have any idea what could possibly be going on?

[Anonymous · Accepted 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.

[v-eachen-msft]

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.

[Anonymous]

@v-eachen-msft 

Thank you so much for your response.

This is how my data is:

• It is a CSV that is cleaned and transformed using python/pandas prior to loading into PowerBI.
• it is a dataframe with date in mm/dd/yyyy as the index starting on July 1 2014 because I want the last 5 fiscal years.  The date is continuous from 07/01/2014 until the current date. If no sales were made on the date then it is entered in as null sales.

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

[Anonymous]

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.