YoY formula not working when filtering out last year's data

[Yohan_Trindade]

Hello

I have created a formula that calculates the YearoverYear increase/decrease of a metric, as per below. However, I can only see data being populated/calculated if I dont filter out 2022 data

YOY NEW =
((sum({Customer Returns(€)})/sum({PCOGS(€)})) - periodOverPeriodLastValue((sum({Customer Returns(€)})/sum({PCOGS(€)})),Date,YEAR,1))*10000

Below is the outcome of such formula

When I filter out 2022 from the analysis, the formula doesnt work/blank is given, as below:

However, I would like to only show 2023 data, and still have the calculation working.
Should I fix the formula or is there another workarounfd?

thanks
Y

[duncan]

Hello @Yohan_Trindade !

I recreated this issue with the following set up:
YOY calc = periodOverPeriodLastValue(sum(Sales), {Order Date}, YEAR, 1)

My thinking here is it’s the function periodOverPeriodLastValue that is causing the problem because when you filter out the data from the previous year it has nothing to compare to. Depending on your desired outcome there could be some options.

Do you want this to be a static line of your pivot table or do you want the whole pivot table to have dynamic filtering? My though here is you could change that calculated field to show the value based on a set of parameters, “start of last year” and “end of last year” to filter the value in the calculated field or using free form lay out and different visuals to pull the data up when its needed.