cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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:

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!

1 ACCEPTED SOLUTION
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])

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

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors