Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@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.
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"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |