Dynamic snapshot/summary of CRM metrics

[steveplatz]

I've been stuck on a problem for some time and can't figure out if it's not possible with Power BI given my current data set or if my knowledge is lacking. I'm trying to create a report for Dynamics CRM that displays opportunity data by month. Where I'm having trouble is summarizing calculated metrics by month and then running a moving average calculation over those summarized metrics.

The input data looks something like this:

I know the date the record was created, when it was closed, and its estimated value.

What I'm trying to create is something like this using the data above:

• Done - Total opportunities - Running total of the number of opportunities created by month end

• CALCULATE(
  	COUNTROWS(Opportunities),
  	FILTER(
  		Opportunities,
  		Opportunities[Created Date] <= MAX('Date'[Date])
  	)
  )

• Done - Total closed opportunities - Running total of the number of opportunitied closed by month end

• CALCULATE(        
          COUNTROWS(Opportunities),
  	FILTER(
  		Opportunities,
  		Opportunities[Is Closed]
  	),
  	FILTER(
  		Opportunities,
  		Opportunities[Close Date] <= MAX('Date'[Date])
  	)
  )

• Done - Total open opportunities - Calculated metric - Difference between total opportunites and total closed opportunities

• [Total Opportunities] - [Total Closed Opportunities]

• Total open estimated value - The sum total of all open opportunity estimated value
• Average open estimated value - The average open estimated value (total open estimated value / total open opportunities)
• 3 Month average open estimated value - A 3 month moving average of the total open estimated value

My question is, is it possible to achieve the last three bullets given the input? Will I need to create a snapshot table of some sort to do this because of the metrics involved? If this is possible, how can I do it? Nothing I've tried thus far has worked!

Thank you for your help!

[Anonymous · Accepted solution]

Hi @steveplatz,

You can refer to below formulas:

Measures:

Total open estimated value = 
 CALCULATE(
	SUM(Opportunities[Estimated Value]),
	FILTER(
		Opportunities,
		Opportunities[Create Date] <= MAX('Date'[Date])&&OR([Is Closed]<>"Yes",Opportunities[Close Date] > MAX('Date'[Date])&& [Is Closed]="Yes")
	)
)

Average open estimated value = [Total open estimated value] / [Total open opportunities]

3 Month average open estimated value = 
var curret=MAX('Date'[Date])
return
AVERAGEX(FILTER(ALL('Date'),[Date]>=DATE(YEAR(curret),MONTH(curret)-3,DAY(curret))&&[Date]<=DATE(YEAR(curret),MONTH(curret),DAY(curret))),[Average open estimated value])

BTW, your formula "Total closed opportunities" seems not works on my side, I modified to below formula:

Total closed opportunities = 
CALCULATE(        
        COUNTROWS(Opportunities),
	FILTER(
		Opportunities,
		Opportunities[Is Closed]="Yes"
	),
	FILTER(
		Opportunities,
		Opportunities[Close Date] <= MAX('Date'[Date])
	)
)

Regards,

Xiaoxin Sheng