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