cancel
Showing results for
Did you mean:
Helper II

## Opportunity Win Rate of last year used as a prediction for next 6 months

I would like to calculate the Opportunities Win Rate (from Dynamics 365) of the last full year (i.e. YEAR minus 1) and us that ratio to weigh Esimated Revenues for the next six months.

I already have a measures that simply calculates Win and Lost opportunities (using some CALCULATE and FILTER on YEAR -1).
It works OK

The problem arises when I try to use that measure to calculate Revenue for the next six Months, where in a Visual I use a "Relative Date" set to "Next 6 Months".
It goes to zero, probably because I am "intersecting" somehting that goes back one year with a filter that show the next six months.

How is it possible to NOT intersesct the filter in the visual with the actual measure and keep the measure the same no matter how I filter it?

4 REPLIES 4
Helper II

This is my measure so far:

``````Estimated Revenue (Weighed) =

VAR Won=
CALCULATE(SUM('Opps'[Estimated Revenue]), FILTER('Opps', YEAR('Opps'[Estimated Closing Date])=YEAR(TODAY())-1
&& 'Opps'[Status]=3 ))

VAR Lost =

CALCULATE(SUM('Opps'[Estimated Revenue]), FILTER('Opps', YEAR('Opps'[Estimated Closing Date])=YEAR(TODAY())-1
&& 'Opps'[Status]=4 ))

VAR
WinRate= Won/(Won+Lost)

RETURN

SUM('Opps'[Estimated Revenue])* WinRate``````
Super User IV

@autodafe , have tried a measure like this with date tbale

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),6,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Proud to be a Super User!

Helper II

Thanks, but that would not work.
I have to create a visual filtered ith a "Relative Date Filter" for the next six months (easy), but multiply that value for the Win Ratio of 2020.
The two date ranges cannot intersect...
I have tried using KEEPFILTERS on the Win Rate but it doesn't work either...

Super User IV

@autodafe , with date table a measure for last year

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Proud to be a Super User!

Announcements