How to calculate YOY % through certain date?


I am trying to get to a certain YOY value, and think there is an easier way to get to it than I am thinking. I am a Power BI user so I am trying to just ‘transfer’ the same logic from Power BI to QuickSight.

I need to display YOY %s in a KPI visual up to a certain date. I have the correct formula to find the YOY % each month for rolling 12 months, but since I have a bunch of blank values prior to the last 12 months, QuickSight is treating these as 0’s and incorrectly skewing my total YOY%.

I have YOY % for each month starting May 2021 - Apr 2022 (this is correctly laid out in a table visual), which is averaging ~80% but since there is no YOY% for months preceding May 2021, it’s dragging down my total YOY% to -37%.

Is there some way I can add a date filter to the calculated field, so that it only calculates YOY for May 2021 - Apr 2022? Here is the formula I am using for Last Year and Year Over Year, but I am not attached to it if there’s a better way to do it. If I add a visual filter to show only the last 12 months or 1 year, it filters out the data I need to calculate YOY.

OrdersYOY=periodOverPeriodPercentDifference( {Orders} , {order_date} , YEAR , 1 )
OrdersLY=periodOverPeriodLastValue( {Orders} , {order_date} , YEAR , 1 )

I have been stuck on this one for awhile, I would greatly appreciate any and all answers. Many thanks.

Hi zwaters! Sorry for the delay. You are using table calculations (periodOverPeriod, etc), which as you correctly pointed out require the prior period data to be part of the query/visual (which is why you cant filter it out). This approach might not work for you table where you show each month, but if you just want a KPI that compares the last 12 months to the 12 months before that I would do it like we explain in this article: Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 7 days, etc)

So yours would look like:
Last 12 months: ifelse(dateDiff({order_date},now(),"MM") <= 12, sales, 0)
Last 13-24 months: ifelse(dateDiff({order_date},now(),"MM") <= 24 AND dateDiff({order_date},now(),"MM") >12, sales, 0)
YoY %: sum(Last 12 Months) / sum(Last 13-24 months) - 1

Then just put that last YoY % into your KPI visual (no filters needed).

Also now() is measuring it relative to the current date. You can change that to a parameter, or you could make another calc that finds the latest date in your data set and use that in here instead (would look like maxOver({your date field}, [ ], PRE_FILTER)

Hi Jesse, no problem at all, thank you for your response!

That article was very helpful, really appreciate you giving an example of each of the type of calculations.

Do you know if those calculations are able to work with date controls? Or do you know if there is a calculation that works specifically with date controls? I have my YOY %, then when I select a date or add a date filter for Mar 2022 it filters out last year’s data, making the formula unable to calculate YOY. Is there some kind of statement I can use that will make the formula ignore filters that are set on the dashboard?

Thank you so much in advance!

Hi Zenia - I answered your question over here: Period Over Period Functions with Date Controls - #3 by Jesse