Forum Discussion

Imex197's avatar
Imex197
Helper I
6 years ago

Using Hitrate for prediction/ trend calculation

Hi there,

 

I need your support. I should use the 12 m average hitrate for calculating the next months in current year.

Formulas for calculating the Hitrate:

HitRate Value = [Hitrate Value Won 12M] / ([Estimated Opportunity Value 12M] - [Hitrate Value Canceled 12M] - [Hitrate Value Misentry 12M])
 
Estimated Opportunity Value ACD =
CALCULATE(
SUM(OpportunityFact[Estimated Opportunity Value]);
USERELATIONSHIP(Opportunity[ActualCloseDate]; 'Date'[Date])
)
 
Hitrate Value Won =
CALCULATE( [Estimated Opportunity Value ACD]; OpportunityState[State] = "Won" )
 
Hitrate Value Won 12M =
CALCULATE(
[Hitrate Value Won];
DATESBETWEEN (
'Date'[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) );
LASTDATE ( 'Date'[Date] )
)
)
 
Estimated Opportunity Value 12M =
CALCULATE
(
[Estimated Opportunity Value CRD];
USERELATIONSHIP('Date'[Date]; Opportunity[CreatedOn]);
DATESBETWEEN (
'Date'[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) );
LASTDATE ( 'Date'[Date] )
)
)
 
Estimated Opportunity Value CRD =
CALCULATE(
SUM(OpportunityFact[Estimated Opportunity Value]);
USERELATIONSHIP('Date'[Date]; Opportunity[CreatedOn]);
ALL(OpportunityState);
ALL(Opportunity[CreatedOn])
)
Hitrate Value Canceled =
CALCULATE( [Estimated Opportunity Value CRD]; OpportunityStatus[Status] = "Canceled" )
 
Hitrate Value Canceled 12M =
CALCULATE(
[Hitrate Value Canceled];
DATESBETWEEN (
'Date'[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) );
LASTDATE ( 'Date'[Date] )
)
)
 
Same logic for misentries.
 
Avg Hitrate 12M =
VAR __LAST_DATE = LASTDATE('Date'[Date])
VAR RollingAverage =
AVERAGEX(
DATESBETWEEN(
'Date'[Date];
DATEADD(__LAST_DATE; -12; MONTH);
__LAST_DATE
);
CALCULATE([HitRate Value])
)
RETURN /*IF(__LAST_DATE > TODAY(); BLANK(); RollingAverage)*/ RollingAverage
 
Example:
Avg. Hitrate last 12M = 10%
Pipeline- Volume for each month:
Estimated Opportunity Value 05/20 = 20 EUR *10% Hitrate = 2 EUR
Estimated Opportunity Value 06/20 = 30 EUR *10% Hitrate = 3 EUR
 
and so on....
 
Thanks in advance!
 
Cheers
Imex197
 
Need Help
Tips and Tricks

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Imex197 ,

     

    Please share a sample data.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Imex197's avatar
      Imex197
      Helper I
      6 years ago

      Hi Lionel,

      I can´t share sample data. Sorry.

      But I can share some pictures.

       

      Target to have in the following months also 46,8 % as Hitrate.

       
       

      Cheers

      Imex197