Forum Discussion
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
- amitchandak
Super User
awff , Try measure like
Averagex(values('Date'[Month Year]), Divide([MTDSUMInvoicedQuotes], [LMTDInvoicedQuotes],0) -1)
- awff
Helper III
amitchandak
As simple as that! I knew i was over thinking