Forum Discussion

MFR2023's avatar
MFR2023
Frequent Visitor
3 years ago

Issue when using Date Add to find Sales Year to Date compared to Sales Prior Year to Date

Hello,

 

I'm having an issue getting Prior Year to Date Sales to work the way I want.

 

I use the following measures:

Sales Current Year To Date = TOTALYTD(Sum(Sales Table[REVENUE]),Calendar_Date[Date])
Sales Prior Year To Date = CALCULATE([Sales CYTD],DATEADD(Calendar_Date[Date],-1,YEAR))
 
Calendar_Date is made by the following formula:
Calendar_Date = CALENDAR(DATE(2018,1,1),Today()-2)
The today-2 is based on the availability of our data only being accurate as of two days ago.
 
My issue is, I want the to date value to display the year-to-date value and last year-to-date value for the current year accurately without having to filter to a specific day. This way whenever someone opens the dashboard the current year to date values are displaying current information accuratly. 
 
As of now, it appears that the Date Add is pulling the full month for the time period if a specific day isn't selected. For example: if we are five days into a month the current year will display correctly (as there is no other data going forward) however the prior year will pull the full month, not just the five days, unless I specify (through a filter/slicer) we are on the 5th day. (specific data has been hidden, but the rows represent the same data points)
No Specified date selected:

 

Most recent day selected:

 

 I have tried changing the PYTD formula to use quarter and days instead of year. Days worked as I wanted it to, however, using -365 days only works on non-leap years. On a leap year (2020 for instance) it pulls the first day of of the year instead. Also, I would imagine that any day's data past February will be off by 1 for prior year.
 
The goal is to get the year to date figure to display so fixing the leap year problem with days or providing an alternative way to use year or a different way to calculate prior year to date would be acceptable.

Thank you!

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    There are many more issues at play here.  First your data source must be in import mode (this will not work in Direct Query mode).  Next you must refresh that data source daily. Then you need to add a "Is Past in Previous Year" flag that is based on your last transaction date minus one year.

     

    And then you can start thinking about the local seasonality. 1st of February this year is guaranteed to be on a different weekday than 1st of February last year. This is much more impactful that the added one day in a leap year.

     

    Thankfully you're not attempting to do Month over Month.  That's where it becomes really ugly.

     

    • MFR2023's avatar
      MFR2023
      Frequent Visitor
      3 years ago

      Hello! I apologize for the delay.

       

      What I'm hearing is, that it is easier for me to update the date filter manually until I can hire a coder to specially design it to consider the time periods over months, quarters, and years.

       

      Thank you for giving me that level of context as it has helped me prioritze where to spend my time.