Skip to main content
cancel
Showing results for 
Search instead 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

Reply
awff
Helper III
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

 

 

awff_1-1644823755748.png

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@awff , add year filter

 

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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@awff , add year filter

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors