You need a calendar table firstly, and you could create it by a Calculated table.
Calendar =
VAR minDate =
MIN ( 'Fact Table'[start date] )
VAR maxDate =
TODAY ()
RETURN
CALENDAR ( minDate, maxDate )
Then, you need a Measure to count cases.
Active Case Number =
VAR num =
CALCULATE (
COUNT ( 'Fact Table'[case] ),
FILTER (
'Fact Table',
'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )
&& ISBLANK ( 'Fact Table'[end date] )
)
)
RETURN
IF ( ISBLANK ( num ), 0, num )
The result looks like this.
Also, attached the pbix file.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
