DAX issue counting records with Start- and EndDates for a given time period and filter

Question

I have the following table ("Services") in PBI:

The table holds all services offered to a customer. A service has a Start- and EndDate.

What I'm trying to do is to create a measure that, for a given date or date range, returns the number of distinct CustomerID:s receiving a service during this period.

Some examples:

Given the table above and a date range between 2019-01-01 and 2019-04-01 the measure would return the distinct value 3 (a match for rows #2, #4 and #5).

Giving a single date of 2019-07-01 the measure would return a distinct value of 3 (because rows #1, #2, #3 and #4 has a period given by Start- and EndDate matching this date).

In my report I also need to be able to filter by ServiceTypeID.

The table is defined like this:

Services = 
DATATABLE (
    "CustomerID"; INTEGER;
    "ServiceTypeID"; INTEGER;
    "ServiceStartDate"; DATETIME;
    "ServiceEndDate"; DATETIME;
    {
        { 1; 10; "2019-06-03"; "2019-09-01"  };
        { 2; 12; "2019-01-01"; "2019-12-31"  };
        { 2; 10; "2019-05-01"; "2019-09-01"  };
        { 3; 8; "2019-02-01"; "2019-08-01"  };
        { 4; 10; "2019-03-30"; "2019-06-01"  }
    }
) 

I have tried defining the measure like the code below, but I have difficulties filtering by ServiceTypeID (the measure just shows a value as if I didn't apply the filter for ServiceTypeID).

Number of active services =
CALCULATE (
    DISTINCTCOUNT ( 'Services'[CustomerID] );
    FILTER (
        ALLSELECTED ( 'Services' );
        (
            MIN ( 'DateTable'[Date] ) >= 'Services'[ServiceStartDate]
                && MIN ( DateTable[Date] ) <= 'Services'[ServiceEndDate]
        )
            || (
                MAX ( DateTable[Date] ) >= 'Services'[ServiceStartDate]
                    && MAX ( DateTable[Date] ) <= 'Services'[ServiceEndDate]
            )
    )
)

Does anybody know what I'm doing wrong here? Any help is much appreciated.

Kind regards, Peter

Answer 1

I propose this solution :

First here is the model :

Then you can write this new measure :

NumActservices = 
 // Retrieve the current date
Var VrCurrentDate = MAX(DateTable[Date])

// Retrieve active Acts filter per ServiceID Within the current date
Var VrServicesInPeriode =  
    CALCULATETABLE(
        Services;
        FILTER(
            ALLEXCEPT(Services;Service[ServiceTypeID]);
            Services[ServiceStartDate]<VrCurrentDate
            &&
            Services[ServiceEndDate]>=VrCurrentDate
        )
    )

// Count Disctinct customer belonging to the previous table
Var VrResult = CALCULATE(   
                    DISTINCTCOUNT(Services[CustomerID]);
                    VrServicesInPeriode
                )


RETURN
VrResult

The result is visible here :