SamePeriodLastYear Formula

[FilippoV]

Hi Community,
I've writing an expression in DAX to calculate the same period of previous year. This is my expression for the current month:
SAMEPERIODLASTYEAR(DATESBETWEEN ( 'Date'[Document Date];"01/03/2017"; TODAY()))

Where TODAY() is 27/03/2017

The problem is that the table result is from 01/03/2016 to 31/03/2016 (not 27/03/2016).

Any Ideas?

Thanks in advanced

[v-shex-msft]

Hi @FilippoV,

I can reproduce your issue. The data between function seems works with static value, but I found if you use this function in other method with dynamic value, the function will get the wrong result.

I'd like to suggest you manually calculate the date fields at power query side.

Notice:

directly use datebetween: both dynamic value and static value works.
Combined datebetween with other method: only static value works.

Regards,

Xiaoxin Sheng

[FilippoV · Accepted solution]

Hi @v-shex-msft,

thanks for your response.

I've observed that if the table Date start from 01/03/2017 to 29/03/2017, not to 28/03/2017 (TODAY), the SamePeriodLastYear works fine.

I don't know the reason but it works.