Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.