My SAP Analytics Cloud (SAC) Journey began 6 months ago and I have been discovering new things every day. I would like to share one of these learnings on how to compute year over year (YoY) dynamically at each level.

SAC has made an effort to simplify things for users by offering a built-in option.

We can write the below formula in the model

YOY([Quantity_sold],[d/Date])     “Quantity_sold” can be replaced by any measure.

This works like a charm till you want to dynamically drill down to quarters or months, then it does not work as expected.

Tried solving this in different ways and came up with nothing. Then posted a question in our community and was provided with few ways to do it.

Below I have tried to list down the steps which worked very well for me

To get the YoY to work dynamically at each level, create new measures in model.

CY (Current Year) – LOOKUP([AMOUNT_USD] ,[d/TIME] =Current(“Year”))

CQ (Current Quarter) – LOOKUP([AMOUNT_USD] ,[d/TIME] =Current(“Quarter”))

PY (Previous Year) – LOOKUP([AMOUNT_USD] ,[d/TIME] =Previous(“Year”,1))

PQ (Previous Quarter) – LOOKUP([AMOUNT_USD] ,[d/TIME] =Previous(“Quarter”,1))

Current (this is to show values based on level of dates)

IF(ISNULL([d/TIME].[p/CALQUARTER] ) ,[CY],[CQ])

Previous (this is to show values based on level of dates)

IF(ISNULL([d/TIME].[p/CALQUARTER] ) ,[PY],[PQ])

YoY (Final Calculation)

([Current]-[Previous])/[Previous]

Below is the final output as a story.

Final Output

There are a few steps to be taken before we get the result. If there are any easier ways to achieve this please do share.

Link to the question I had posted.

https://answers.sap.com/questions/14019684/lookup-function-for-yoy-with-drilldown.html