lookup function for YOY with drilldown

[vrajgopal]

I am trying the to create a YoY view with drilldown to quarters.

LOOKUP([ACV] ,[d/Date]=Previous("Year",1))

LOOKUP([ACV] ,[d/Date]=Current("Year"))

The above formula works if am comparing 2023 with 2022. If i want to drilldown and compare say Q1'23 with Q1'22 it does not.

Is there a way I can include quarterly calculation (LOOKUP([ACV] ,[d/Date]=Previous("Quarter",4))) in the same formula with the year??

regards

Vish

[N1kh1l · Accepted solution]

vrajgopal

vrajgopalOk I think I understand your issue. You want to do YoY dynamically on each level of Date i.e. Year/Quarter/Month.

Create 3 measures each for Prev Year, Prev Qtr and Prev Month

PY

LOOKUP([No_of_pax] ,[d/Date]= Previous("Year", 1))

PQ

LOOKUP([No_of_pax] ,[d/Date]= Previous("Quarter", 4))

PM

LOOKUP([No_of_pax] ,[d/Date]= Previous("Month", 12))

Now create a final formula PREV to display one of the above based on the level of Date

IF(ISNULL([d/Date].[p/CALQUARTER] ) ,[No_of_pax_PY] ,IF(ISNULL([d/Date].[p/CALMONTH] ),[No_of_pax_PQ] ,[No_of_pax_PM]) )

Now you can just do a YoY

(["SAC Flight":No_of_pax]/["SAC Flight":No_Of_Pax_Prev])-1

Output:

Hope this helps !!

Please upvote/accept if this helps

Br

Nikhil

[vrajgopal · Accepted solution]

Thanks will work on it

[vrajgopal · Accepted solution]

Did a small modification as I do not have month data. And added another formula for current

IF(ISNULL([d/Date].[p/CALQUARTER] ) ,[Cyr],[CQ])

Now it works like a charm.

Thank you

[peersbrennanctb · Accepted solution]

Hi i have performed all the steps but my PREV calc will only display when the date is drilled down to the lowest hierarchy level. @N1kh1l 

[N1kh1l]

vrajgopal

vrajgopalAny reason of not using the standard YoY formula available in Account formulas ?

YOY([No_of_pax] ,[d/Date])

Output:

From 2023 to 2024 value went from 22000 to 26000 which is 18% growth and is correctly reflected. The reason its same for Year Quarter and months is because the value were entered at year and hence equal distribution. But YoY will show you data at all levels of Date dimension.

Br.

Nikhil

[vrajgopal]

I tried . When i try to drill it was repeating the growth rate. I will try again.

[geeta2021]

Hello Vishwanath,

You can create calculated measures with Input controls that should work.

Thanks & Regards,

Geeta

[vrajgopal]

Thanks will try an implement it.

I came with another workaround of using radio buttons and giving a yearly and quartery view