Count of case, if created date is between 2 seperate date slicers

[omillzy]

I'm trying to count the number of contracts (contract id), if the contract start date falls between the selected date slicer.

The trouble is, I have a tricky business requirement to use two date slicers (see pic below): the 'business support case' commencement date, and 'business support case' end date. The data looking at cases that can span over several years, so the user wants to be able to see cases that are open in the period selected (but might have opened at any point in the past) hence the dual slicer.

I've seen several solutions using variables but they seem to rely on a central date table which I can't have as I am using two date fields. Any ideas how to achieve this?

Ignore the many to many relationship - it should be one to many flowing from top to bottom (to both tables). power just isn't recognising the one to many-ness for some reason.

[v-jayw-msft]

Hi @omillzy ,

Just a thought, how about creating a CALENDAR table and create several inactive relationships then create measure using USERELATIONSHIP() function to active the relationships when doing the calculation.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[omillzy]

@v-jayw-msft I need the date slicer to interact with both date columns (commencementDate and endDate), which is why I've seperated them out - is this possible with a calendar table and USERELATIONSHIP()?

From what I understand the USERELATIONSHIP() function is more for DAX and calculated columns and can't be used on a slicer visual to 'activate' the inactive relation to the other date column

[v-jayw-msft · Accepted solution]

Hi @omillzy ,

Please try.

measure = calculate(sum('table'[column]),filter('table',column1=SELECTEDVALUE(slicer)),USERELATIONSHIP(columna,columnb))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.