cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.