Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SamePeriodLastYear stopped working within YTD calculation

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?
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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--4zOk0MlDmwXJ7a2gG8UB5V2Pf5sy36JMy2yonNIanw?e=HmiMgZ ---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's avatar
    Anonymous
    Not applicable
    7 years ago

    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.