topic Re: Opportunity Win Rate of last year used as a prediction for next 6 months in DAX Commands and Tips

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

autodafe — Tue, 09 Feb 2021 11:18:14 GMT

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?

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

amitchandak — Tue, 09 Feb 2021 11:57:15 GMT

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.

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

autodafe — Tue, 09 Feb 2021 12:01:37 GMT

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

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

autodafe — Tue, 09 Feb 2021 12:15:46 GMT

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

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

amitchandak — Tue, 09 Feb 2021 12:38:40 GMT

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