Count active records if between two dates

[Anonymous]

I've got 2 tables, see examples here. In reality these tables are of course way bigger.

Date	YearMonth
1-1-2010	2010-01
2-1-2010	2010-01
3-1-2010	2010-01
4-1-2010	2010-01
5-1-2010	2010-01
6-1-2010	2010-01

TherapyID	StartDate	EndDate	Category
1	1-2-2010	2-4-2010	North
2	14-2-2010	15-4-2010	South
3	27-2-2010	28-4-2010	West
4	12-3-2010	11-5-2010	North
5	25-3-2010	24-5-2010	North
6	7-4-2010	6-6-2010	West

I want to have a graph with on the X-axis the YearMonth and on the Y-axis I want the number of active Therapy's. So IF StartDate <= last day of that month (YearMonth) AND IF EndDate >= first day of that month (YearMonth).

Of course I do want to add a slicer for the category. Should I rearrange the tables of is there a smart calculation I could use?

[Fowmy]

@Anonymous 

Add this as a measure to your table 1, in your chart, keep the Year Month field and use the measure. Rename Table1 and Table 2 and the column names as per your tables.

_Count = 
VAR __minDATE = MIN(Table1[Date])
VAR __maxDATE = MAX(Table1[Date])
RETURN
COUNTROWS(
    FILTER(
        'TABLE2',
        'TABLE2'[Start Date] >= __minDATE &&  'TABLE2'[End Date] <=  __maxDATE ) 
    )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks, it wasn't the formula I was looking for but with your help I got the formula I wanted!

Maybe I didn't describe it correct, but the following formula gave me the result I wanted:

_count = COUNTROWS(Table2)-COUNTROWS(FILTER(Table2,Table2[StartDate]>=[VAR_maxDate]||Table2[EndDate]<=[VAR_minDate]))

[Anonymous]

@Fowmy

I've got now 3 Measures:

_MinDate = MIN(Table1[Date])

_MaxDate = MAX(Table1[Date])

_count = COUNTROWS(FILTER(Table2,Table2[StartDate]>=[VAR_maxDate]&&Table2[EndDate]<=[VAR_minDate]))

With the table (see below) I want the following answers of how many Therapy's are active:

Feb-10: 3

Mar-10: 5

Apr-10: 6

May-10: 3

Jun-10: 1

The Measure doesn't give me this answers, am I doing something wrong?

TherapyID	StartDate	EndDate	Category
1	1-2-2010	2-4-2010	North
2	14-2-2010	15-4-2010	South
3	27-2-2010	28-4-2010	West
4	12-3-2010	11-5-2010	North
5	25-3-2010	24-5-2010	North
6	7-4-2010	6-6-2010	West

[Fowmy · Accepted solution]

@Anonymous 

You don't have to create three measures, but what you have done is fine. The issue is you have used the [VAR_minDate] and [VAR_maxDate] the other way around. 

I corrected it as below, please try now

_count = COUNTROWS(FILTER(Table2,Table2[StartDate]>=[VAR_minDate]&&Table2[EndDate]<=[VAR_maxDate]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn