Average Deal Age (Days)

(0) Share

Report

Posted on by Community Member

Hello,

Do you know how can I calculate Average Deal Age? The data is coming from Odata for Microsoft Dynamics CRM 2016 (v8).

I'm using PowerBI and in the pre-made templates they a calculation that I can't access call Average Deal Size. Do you know how they make this calculation?

A screenshot below.

I'm guessing that this information is not possible to retrieve by Odata, but I'm not sure. The URL of my connection to Odata looks like this:

companyname.crm4.dynamics.com/.../OrganizationData.svc

Regards,

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Dynamics_Alok 1,746 on at

Like (0)

Report
Copy link

Link copied!

Since You are using Dynamics CRM online data with PowerBI hence data is stored on Microsoft cloud and query running on the same data is also published on Cloud so AFAIK you can't do reverse engineering .

Was this reply helpful? Yes No
tw0sh3ds 5,600 on at

Like (0)

Report
Copy link

Link copied!

Why do you need to get this value from OData? the idea of Power BI (any BI in fact) is to make data transformations in Power BI after getting raw data from external systems. There are many samples on google when you look for calculating average value, here is some of the first that came up for me:

docs.microsoft.com/.../service-aggregates

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hello tw0sh3ds,

Thanks for your response. The question is more oriented to know what is the formula behind the Average Deal Age rather in how to use PowerBI.

I have tried to replicate the calculation without success. I was thinking to do:

[modified date - created date] (for a given opportunity)

I'm not getting the same numbers.

Do you know how this number is calculated? Either generated by Dynamics or I can calculate on PBI.

Regards,

Was this reply helpful? Yes No
Verified answer

Sindre Wetting on at

Like (1)

Report
Copy link

Link copied!

The formula would be omething like this, and I'm assuming this issue is regarding open Opportunities:

For every open opportunity

ThisOpportunityDealage = daysbetween (today - created)

Totalage = Totalage +ThisOpportunityDealage

Avgdealage = Totalage / number of open opportunities.

If you're including closed dels, you'll havet to check status open/closed and user the correct timestamp; created vs actualclosedate.

Was this reply helpful? Yes No