Forum Discussion

zivhimmel's avatar
zivhimmel
Icon for Resolver I rankResolver I
5 years ago

SAMEPERIODLASTYEAR weird behavior when including today

Hi,

I have used SAMEPERIODLASTYEAR to create a measure showing me values of the previous year.

 

Guests = DISTINCTCOUNT('Fact Guest Enquiry'[enquiry_id])

Guests Previous Year = CALCULATE(Guests,SAMEPERIODLASTYEAR('Dim Date'[Date]))

 

There's a relationship between 'Fact Guest Enquiry' and Dim Date (fact.enquiry date and dim.date).

In my report I have a date filter. Users can choose a range and I display both the guests for the range chosen, and the figure for the same period last year, using the measure shown above.

 

This works very well, except for one case, which is when today is included (either by choosing a specific date range which includes today, or by using a relative filter and chosing to include today). When today is included, the figures for this year are correct, but for same period last year, they jump up by a few hundreds.

I verified and these few hundreds are not legit - there aren't such records in the previous year.

 

Any ideas? Thanks.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    zivhimmel , Make sure your date/calendar table is marked as date table. This should work with today too.

     

    Try this type of formula

    Year behind = CALCULATE([Guest]),dateadd('Dim Date'[Date],-1,Year))

     

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

    • zivhimmel's avatar
      zivhimmel
      Icon for Resolver I rankResolver I
      5 years ago

      Thanks amitchandak . I tried your suggestions. Marked the table as a date table and tried your DAX formula.

      Unfortunately it returns the same result.