help in YoY growth calculation

[aryaneski_]

Hello all, I am a newbie and could not make anything out of the answers in previous questions. Im not so sure what I'm doing wrong but basically I want to have a column computing the YoY growth of X values found in column X. (e.g january 2023 versus january 2022, 2022.Q1 versus 2021.Q1).

I tried the -Before() function and tried sums/counts  but could not make it work. Could you help me out? Thanks!

Date: Ref_Date.Year ; Ref_Date.Month ; Ref_Date.Quarter
X and Y are (count (distinct X)) and (Count (distinct Y))

[deepanshuSh]

Hi, Could you clarify more on your requirement, as what do you mean by comparison, because if you just want to do know the changes between last year to current year, you can just do it by 

(count (distinct X))/ (Count (distinct Y)) -1

[stevedark]

Hi @aryaneski_ 

What you want to do is to remove the Year as a dimension and then apply that using Set Analysis in different measures.

If you wanted to fix to current calendar year vs prior you could do this for the current year measure:

sum({<Year={$(=year(today()))}>}Value)

And then this for the prior year measure:

sum({<Year={$(=year(today())-1)}>}Value)

You could then add a further expression for the variance:

(sum({<Year={$(=year(today()))}>}Value) - sum({<Year={$(=year(today())-1)}>}Value)) / sum({<Year={$(=year(today())-1)}>}Value)

I have done a blog post on Prior Period Comparison which goes into a lot more detail of what you can do, and has an example app you can download to see it working:

https://www.quickintelligence.co.uk/prior-period-comparison/

Hope that helps.

Steve

[aryaneski_]

Thanks @stevedark! I have years from 2020 to 2023 tho so I would need to have a year on year growth depending on the year and not just the current reporting period.

(e.g yoy growth % for 2022 January X value versus 2021 January X value)

I will check out your blog post!

[aryaneski_]

Hello @deepanshuSh  - X and Y are different values and cannot be compared to each other.

I want to get yoy growth for example for X value on January 2022 versus X value on January 2021, etc. 

[stevedark]

Hi @aryaneski_ 

What you may want to do then is replace the part in $() with a calculation based on your data and selections, for instance:

sum({<Year={$(=max(Year))}>}Value)

The app attached to the blog post has a number more examples.

Steve