Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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:
I've got now 3 Measures:
_MinDate = MIN(Table1[Date])
_MaxDate = MAX(Table1[Date])
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |