cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steveplatz
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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.