Forum Discussion

SMB-Qtech's avatar
SMB-Qtech
New Member
8 years ago

LastPeriodSamePeriod Giving wrong values when including last date of the calendar

I have some reports that I'm trying to calculate the LY period of Sales data. The Sales Data is always behind by a couple days in the SQL table.

 

I created a calendar date of all sales data ship date records.   I use this date for my slicer.

Calendar = CALENDAR(Min(SalesData[Ship Date]),MAX(SalesData[Ship Date]))

 

LY Yards = CALCULATE(SUM(SalesData[Yards]),SAMEPERIODLASTYEAR('Calendar'[Date]))

Everything works great except when I use the last date from the file 1-1-2018 to 1-17-2018 then the LY Yards is some erroneous number way off. 

If I change the Calendar function to

Calendar = CALENDAR(Min(SalesData[Ship Date]),(today()))

Then 1-1-2018 to 1-17-2018 works fine. 

It is not a data issue.  I don't want to use the Today because we want to show the slicer of what dates truly exist in the data.

Tomorrow 1-17-2018 will work and 1-18-2018 won't.

This is happening on all of my Last Year calculations.

I would appreciate any advice. 

 

 

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Any chance you can share sample PBIX? This sounds strange and I don't think it is solveable without sample data. The dates returned should be the same as the dates returned from:

     

    DATEADD(dates, -1, year)

     

    So, perhaps try substituting that or create a new table and see what is being returned in terms of dates when using your last date in the file.

    • SMB-Qtech's avatar
      SMB-Qtech
      New Member
      8 years ago

      So where should I substitute DATEADD(dates, -1, year) in the formula?

       

      LY Yards = CALCULATE(SUM(SalesData[Yards]),SAMEPERIODLASTYEAR('Calendar'[Date]))