Year on year growth and p3m growth on normalized data

[danisharoon]

Hi there

I have a fact and dimensions each, which are as follows:

fact_volume

dim_date

I want to find the year on year growth, such that if I select August 2022 from the my slicer, it should calculate using August 2021, and August 2022.

Also, I want to calculate GP3M, such that if I select August 2022 from the my slicer, it should calculate P3M first i.e. average of last 3 months (May-July 2022). Then use P3M and August 2022's volume to calculate GP3M.

Link to the dataset is as follows:

https://docs.google.com/spreadsheets/d/12z33RQ4_0BQMhcOVk3yKiDLzhT1b6QKr/edit?usp=sharing&ouid=11501...

Danish

[FreemanZ · Accepted solution]

hi @danisharoon 

try like:

GYoY =

VAR _volumn = SUM(fact_volume[Volumn])

VAR _volumnpy =

CALCULATE(

    SUM(fact_volume[Volumn]),

    DATEADD(dim_date[date name], -1, YEAR)

)

RETURN

DIVIDE(_volumn, _volumnpy) - 1

GP3M = 

VAR _volumn = SUM(fact_volume[Volumn])

VAR _volumn3pm =

CALCULATE(

    SUM(fact_volume[Volumn]),

    DATESINPERIOD(dim_date[date name], EDATE(MAX(dim_date[date name]), -1), -3, MONTH)

)

RETURN

DIVIDE(_volumn, _volumn3pm/3) - 1