cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculating average monthly growth rate to reset every year

Hi PBI'ers

I'm calculating the average monthly growth rate (average of all monthly % changes) on a year on year basis.

The below DAX is what I have, and gives the following result:

``````AverageMonthlyGrowthRate (AMGR) = //average of MTD vs LMTD % diff for MoM growth
VAR InvoicedAvg =
Calculate(
Averagex(
values('_Dates'[month&year]),
Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1))
VAR Result = If(_Measures[MTDSUMInvoicedQuotes] = BLANK(), blank(), InvoicedAvg)
RETURN
Result``````

Essentially, i want to be able for January to 'reset' every year, and only compare feb onwards.

1 ACCEPTED SOLUTION
Super User

AverageMonthlyGrowthRate (AMGR) = //average of MTD vs LMTD % diff for MoM growth
VAR InvoicedAvg =
Calculate(
Averagex(
values('_Dates'[month&year]),
Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1),
filter('_Dates', '_Dates'[Year] = max('_Dates'[Year])) )
VAR Result = If(_Measures[MTDSUMInvoicedQuotes] = BLANK(), blank(), InvoicedAvg)
RETURN
Result

Super User

AverageMonthlyGrowthRate (AMGR) = //average of MTD vs LMTD % diff for MoM growth
VAR InvoicedAvg =
Calculate(
Averagex(
values('_Dates'[month&year]),
Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1),
filter('_Dates', '_Dates'[Year] = max('_Dates'[Year])) )
VAR Result = If(_Measures[MTDSUMInvoicedQuotes] = BLANK(), blank(), InvoicedAvg)
RETURN
Result