cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
autodafe
Helper II
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
autodafe
Helper II
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
amitchandak
Super User IV
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!

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...

 

@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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

secondImage

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