cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steveplatz Frequent Visitor
Frequent Visitor

Dynamic snapshot/summary of CRM metrics

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:

Input.PNG

 

 

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:

 

Result.PNG

 

 

  • 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic snapshot/summary of CRM metrics

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])

 

Capture.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamic snapshot/summary of CRM metrics

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])

 

Capture.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |