Count Open Opportunies - ALL and FILTER problem

[IanR]

Hi,

I am trying to create a measure in a CRM report that shows how many opportunities were open at the end of a filtered date period, the filter being provided by a slicer on the dates table. The measure will be placed in a table with opportunity owners on rows so it needs to be filterable/sliceable by owner. In our CRM opportunities have a created on date (CreatedOn) and an closed date (ActualCloseDate). ActualCloseDate is when an opportunity stops being open. I’m pretty sure that the logic I need is this:

Count opportunity table rows where 
CreatedOn is on or before the last day of the filter 
AND
(ActualCloseDate is after the last date in the filter OR ActualCloseDate is blank)

The problem that I’m having is getting this to work in DAX.
The Opportunities table has a many to one relationship with a Users table that provides the filtering on the opportunity owner in the rows of the final table.
The opportunities table also has a many to one relationship with the dates table, from CreatedOn to Date.

Because of this last relationship I need to do something similar to ALL(Opportunities) in my filter so that I can include opportunities created before the filter period but exactly what I can’t work out yet. Simplifying for readability what I’ve used in the code below is:

FILTER(ALL([ActualCloseDate], [CreatedOn])…..).

What this doesn’t seem to do is overturn the filter on CreatedOn that comes from the relationship with the dates table. The values that I'm getting from this measure are all too low and I think this is because only opportinities that were created in the filter period are being credated. This isn't what I need. If I'm filtering on week 29, for example, I want to know how many opportunities were open at the end of that period - no matter when they were created.

This is my formula. I’m using a variable in the belief that this lets me get away with not bending my mind around any context transitions that might be taking place – I hope this is valid.

penOps All2 = VAR LastDayInFilter = CALCULATE(MAX(Dates[Date]))
		RETURN
		CALCULATE(COUNTROWS(OpportunitySet), 	
				
		FILTER(ALL(OpportunitySet[ActualCloseDate], OpportunitySet[CreatedOn]),
			OpportunitySet[CreatedOn] <= LastDayInFilter
			&&
		(	OpportunitySet[ActualCloseDate] > LastDayInFilter
			||
			OpportunitySet[ActualCloseDate] = BLANK()
		)
)
)

I'm stumped. Can anybody advise?
Ian

[v-sihou-msft]

@IanR

In this scenario, I think your formula is correct.  If you want to count rows for all opportunities instead the selected created entries, you have to disable the relationship between Opportunity table and Dates table. Otherwise, once you select date period in slicer, it will filter the Opportunity table context.

Regards,

[IanR]

Hi Simon_Hou-MSFT ,

Is there a way to disable a relationship from within a measure? I was hoping that USERELATIONSHIP would have an option to use no relationship but if it's there I couldn't find it.

Thanks

Ian

[IanR]

Any idea why my ALL(OpportunitySet[ActualCloseDate], OpportunitySet[CreatedOn]) isn't removing the filter applied by the slicers?

There are two slicers (I don't know if that is relavant) one on the year and one on the week number, both fields in theDates table. The Dates table has a relationship with the opportunities table, via the CtraetedOn field in the Opportunities table, but I thought my ALL(...) statement was overruling that. It doesn't seem to be.

The only function of the slicers in this report is to get me the last date in the filter (the end of the selected week). Unfortunately I cannot permanently remove the relationship because other charts and tables in the report use it.

Thanks

[IanR]

Does anybody have any ideas?

[OwenAuger]

@IanR

I recommend you follow the method discussed in these articles:

https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

http://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplificat... (by @ImkeF)

Basically, you create a transformed table which contains columns Opportunity & Date (and any others required), where there is one row for each date a given Opportunity is open. You can leave your original table in the model as well if it is convenient for other measures.

When creating the transformed table, you would just have to be careful with the last date for each Opportunity. From your description I think each Opportunity would have rows corresponding to dates from CreatedOn to ActualCloseDate - 1, since the Opportunity is deemed to be closed on ActualCloseDate.

Also since ActualCloseDate can be blank, you may want to use the DATESBETWEEN function when following the method in the above articles (DATESBETWEEN treats blank as unbounded).

Your Dates table would then have to be related to the single date column in the transformed OpportunitySet.

To then count the opportunities active on the last date in the current context, you would use a measure like:

Opportunities active on last selected date =
CALCULATE ( 
    DISTINCTCOUNT ( OpportunitySet[OpportunityID] ),
    LASTDATE ( Dates[Date] )
)

There is also the option of the 'classic' events in progress DAX pattern discussed in this paper:

http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

I would suggest having a go with this method. Please post back with sample data or a pbix file if you would like help building this.

Regards,

Owen 🙂