Forum Discussion

awff's avatar
awff
Icon for Helper III rankHelper III
4 years ago
Solved

Help with Average Monthly Growth Rate

I'm not sure if i'm overthinking things or PBI has it more complicated that it should.

I'm trying to get the average monthly growth rate for the years into a visualisation. I was hoping it was as simple as making a measure to average another measure...

Here's what i have so far:
Total invoiced (Just finding the total invoiced value with a few filters): 

 

TotalInvoicedQuotes = 
Calculate(SUM('_fact (Quote)'[Total_Price_USD__c]),'Opp Stages'[Probability] = 100,Quote[Type_of_Transaction__c] = "Invoice Request",
USERELATIONSHIP(_Dates[Date],Quote[Invoice_Request_Date__c]))

 

MTD total & Last Month total of invoiced:

 

MTDSUMInvoicedQuotes = 
CALCULATE(TOTALMTD([TotalInvoicedQuotes], '_Dates'[Date]), Filter(_Dates, _Dates[Date] <= today()))
LMTDInvoicedQuotes = 
Calculate([MTDSUMInvoicedQuotes],dateadd(_Dates[Date], -1, MONTH))

 

Which is where i got the MoM% growth:

 

% Change MoM = 
Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1

 

 

I've seen other posts using SUMMARIZE and AVERAGEX but can't seem to get it working as i'm using a measure. The SUMMARIZE methods online aslso seem to be using a local date table rather than a date dimension table i use.

 

Below is the table i have so far:

 

Help is greatly appreciated!

  • awff ,  Try measure like

    Averagex(values('Date'[Month Year]), Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1)

2 Replies

  • awff ,  Try measure like

    Averagex(values('Date'[Month Year]), Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1)