Unexpected behavior Sameperiodlastyear

[cvanderheijden]

Hi,

I'm using the function sameperiodlastyear in some measures and they work fine for all periods besides the current month. I would expect to see the MTD value of lastyear's data there but it shows the full month's value.

Some information:

- I have a dedicated date table with continuous dates created up until today's date

- I have slicers for business year and business month

- I have tried adding a slicer for days. When I select the number of days up until yesterday's date it gives me the correct values in the matrix (both this year and lastyear) but as soon as I select all days MTD, lastyear's values jump to the total month value.

I don't understand why this is happening. Here are my measures and an example of the jump in value:

Total Volume = calculate(sum('Eod brokerspread'[volume]))

Total Volume LY = CALCULATE([Total Volume],SAMEPERIODLASTYEAR('Date'[Date]))

19th of April not included:

gives this result:

19th included gives this result:

Total Volume LY above is the total of April last year's value and not between the 1st and the 19th.

Can someone explain why it treats sameperiodlastyear as a full month as soon as all dates month-to-date are included?

Btw i've tried changing sameperiodlastyear with the following formula with exactly the same result.

Total Volume LY = CALCULATE(sum('Eod brokerspread'[volume]),DATESMTD(dateadd('Date'[Date],-1,year)))

Also read this blogpost already where it explains that sameperiodlastyear and the above logic are the same:

http://mdxdax.blogspot.com/2011/01/dax-time-intelligence-functions.html

Thanks,

Chris

[aj1973]

Hi @cvanderheijden 

Try to use "day" as interval for your DATEADD instead of year.

[cvanderheijden]

Hi Amine,

thanks for your answer but this does not fix my problem, because I want to look at the same period but 12 months ago, not yesterday.

Chris

[aj1973 · Accepted solution]

@cvanderheijden 

Did you make it look like this? dateadd('Date'[Date],-365,day)

-1 is yesterday , -365 goes back to 12 months.

[cvanderheijden]

Hi Amine,

you are right, I read your solution to quickly. I have another visual where sameperiodlastyear shows me the correct value on a per year basis and your solution gives a slightly different value. Do you maybe know why? I've changed "-365" to different days to see if I could match the value from sameperiodlastyear but I cannot.

What could be the logic behind this difference?

Thanks,

Chris 

[aj1973]

@cvanderheijden 

The difference is in the granularity of data you want to see in your visual. PREVIOUSYEAR returns the result of the entire previous year, DATEADD returns back the number of days from the date you want to start your calculation. Nothing wrong with either approach, it is just how the user wants to see the data in a specific visual.

Check out this article may it could help.

https://minova.nl/geen-categorie/power-bi-dax-dateadd-versus-previousmonth

https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question

[cvanderheijden]

Ok clear, thank you!

[cvanderheijden]

@amitchandak thanks for your reply, but I already explained in my question that I had tried this already, but I wrote the expression wrong to include DATESMTD. 

I have tried your solution again but with the same result. Also as I have included in my post, your solution and sameperiodlastyear are the same as explained in this blogpost -> http://mdxdax.blogspot.com/2011/01/dax-time-intelligence-functions.html

It also does not make sense to me that it would recognise we are only into April (instead of showing last year's full volume) correctly when only selecting the Year in the slicer, but not that we are only onto the 19th day.

Does anyone else have any ideas?

[amitchandak]

@cvanderheijden ,Please try like this with a date table

Year behind Sales = CALCULATE(sum('Eod brokerspread'[volume]),dateadd('Date'[Date],-1,Year))