Count ID that has startdate on or before 10th of each month and enddate is after 10th of that month

[NG1407]

I need to find the count of only those ID that has startdate <= 10 and enddate > 10 for each month . In other words need to find count of ID that are active till current selected month. A record is active if its enddate is greater than 10th of that month.

For example for jan month I need to count all those ID that has startdate less than or equal to 10thjan and startdate should contain dates from previous month as well that has end date > 10thjan or in future).

 below is the sample data : 

Startdate	enddate	ID
2024-01-10	2024-12-01	a123
2023-12-11	2024-01-11	b123
2024-01-02	2024-11-08	c123
2024-02-11	2024-02-28	d123
2024-03-03	2024-03-10	e123
2024-03-03	2024-03-15	f123

According to above data for jan month the count should be 3, for feb month count should be 0,for march its 1.

I need to display data in line graph. The issue is its filtering the data and showing only jan month data not december month.

[NG1407]

Thank you 🙂 @johnt75 

[johnt75 · Accepted solution]

You can use

Num active =
VAR CurrentMonth =
    MAX ( 'Date'[Date] )
VAR CutOffDate =
    DATE ( YEAR ( CurrentMonth ), MONTH ( CurrentMonth ), 10 )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        'Table'[Start date] <= CutOffDate,
        'Table'[End date] > CutOffDate
            || ISBLANK ( 'Table'[End date] ),
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

If you know that there is only 1 row per ID then you could use COUNTROWS('Table') instead of DISTINCTCOUNT which might be faster.