Forum Discussion
Count active records if between two dates
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?
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 🙂
4 Replies
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 🙂
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: 3Mar-10: 5Apr-10: 6May-10: 3Jun-10: 1The 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
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 🙂
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]))