Forum Discussion
Average Growth Rate
vivek_rana , using a separate date or year table
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
Now create Avg diff %
Avg diff % = AverageX(values('Date'[Year]), [diff %])
Hi amitchandak
How can we do it at Year -month level. I tried replacing Year with Yearmonth field from calender table..but seems like its not working... Can you please suggest here.
What result are you expecting? Please show that clearly.
Ashish_Mathur .- I have this Output right now, with month over month percentage difference calculated.
Date(Month/Year) Amount MOM Percentage Difference May 2022 7468 -80% April 2022 36522 -38% March 2022 58872 190% Feb 2022 20301 -54% Jan 2022 43971 -55% Dec 2021 96699 I am trying to show Average of MOM Percentage Difference in a CARD.
so I think output should be (-80+(-38)+190+(-54)+(-55))/5 = -7.40.
Please correct me if i am wrong.
Sharing Calculation for MOM Percentage Difference :
MoM% =VAR Sum_CY = SUM ( Orders[Sales] )VAR Sum_PY =CALCULATE (SUM ( Orders[Sales] ),ALLEXCEPT (Orders,Orders[Category]),DATEADD ( 'Calendar'[Date].[Date], -1, MONTH ))VAR MoM =DIVIDE ( Sum_CY, Sum_PY ) - 1Var Result =IF (MoM = -1,BLANK (),MoM)Return Result