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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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"))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors