DAX for calculating YOY % change does not work

[alicewang96]

I'm trying to calculate year-over-year % change. I've created three measures to do this: Total, PY Total2, % Change.

The formulas I've used are as follows:

1. Total:

Total = SUM(data[Monetary Amount])

2. PY Total2:

PY Total2 = calculate(sum(data[Monetary Amount]),sameperiodlastyear(data[Posting Date]),all(data))

3. % Change:

% Change = divide([Total]-[PY Total2],[PY Total2],0)

All the values are being calculated correctly, with the exception of the two rows highlighted in the photo above (Total 2018, PYTotal2 2019).  Can anyone tell me where I've gone wrong? Thank you in advance!

[Anonymous]

This depends on how your dataset is, but you could do something like this

PY Total2 = 
VAR cYear = IF(HASONEVALUE(data[Posting Year]),VALUES(data[Posting Year]))

RETURN
IF(
  NOT(ISBLANK(cYear)),
  CALCULATE(
    [Total],
    data[Posting Year] = (cYear - 1),
    all(data)
  )
)

[Anonymous]

I would guess that in the Total 2018 you have the full year and in the PY Total (2019), you only have Jan-Sep 2018 since within 2019 you do not have dates for the full year. Hence, sameperiodlastyear will shift the dates from Jan-Sep 2019 to Jan-Sep 2018 and that is what you are seeing in PY Total for 2019.

Regards,

Kristjan

[alicewang96]

Thanks for your response! Do you know how I can fix this formula to be accurate if we don't have all of our 2019 date yet?

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...