cancel
Showing results for 
Search instead for 
Did you mean: 

lookup function for YOY with drilldown

vrajgopal
Explorer
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

N1kh1l
Active Contributor
0 Kudos

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
Explorer
0 Kudos

Thanks will work on it

vrajgopal
Explorer
0 Kudos

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

Answers (2)

Answers (2)

N1kh1l
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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

geeta2021
Explorer
0 Kudos

Hello Vishwanath,

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

Thanks & Regards,

Geeta

vrajgopal
Explorer
0 Kudos

Thanks will try an implement it.

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