SAMEPERIODLASTYEAR weird behavior when including today

[zivhimmel]

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.

[v-shex-msft]

Hi @zivhimmel,

I'd like to suggest you use date function to manually defined the calculation ranges, it is more agility than time intelligence functions and not required a complete calendar table.

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[amitchandak]

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

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

Unfortunately it returns the same result.