Month not tied to single criteria

[Anonymous]

Hi,

So I have landed up in a unique problem here :-

I have a date table and my dataset. My dataset is directly connected to the date table. My dataset is something similar to this: -

TaskID	Open Date	Closed Date
1	1/1/2014	2/3/2016
2	3/4/2016	6/7/2017
3	9/1/2016	5/12/2017
4	7/31/2015	1/5/2018

Now I want to plot a visual with number of tasks open and closed in the past 3 years (one visual with 2 lines). However, my date table is connected to either closed or open date. How do I fix this? I do not want to connect my date table. Just want to use that as a reference. 

[Ashish_Mathur]

Hi,

Try this

1. The relatioshop from the Open Date to the Date in the Calendar Table should be active
2. The relatioshop from the Closed Date to the Date in the Calendar Table should be inactive
3. Drag the Date from the Calendar Table in your visual
4. Set the relative date filter to past 3 years
5. Write these measures

Cases opened = COUNT(Data[TaskID])

Cases closed = CALCULATE(COUNT(Data[TaskID]),USERELATIONSHIP(Data[Closed Date],Calednar[Date]))

Hope this helps.

[MFelix · Accepted solution]

Hi @Anonymous,

Make two inactives relationships between the date table and open/closed date then make two measures somilar to below

CALCULATE(COUNT(table[taskid]) ; USERRELATIONSHIP(table[opendate]; calendar[date])

Create one for open like above and another for closed then add it to your visual with date from.calendar table as axis.

Regards
MFelix