Month on Month : Count of Active Services

[ToddMate]

Hi,

I’m trying to build a matrix that will count the number of services I have that are active as at the close of each month.

For example we sell subscription services that have a Connected date and a Disconnected date, what I am trying to do is count the number of active services as at the end of each month.

To determine an active service the Connection date must be prior to the end of the month and the disconnection date was greater than the end of the month.

In terms of structure, each row of the table stores the unique Service ID along with the Connected date and Disconnected date on the same row.

Can anyone help.

Cheers

Todd

[v-lionel-msft · Accepted solution]

Hi @ToddMate ,

My original data:

First, I create a table with DAX:

Table = CALENDAR(DATE(2019, 5, 1), DATE(2019, 12, 31))

Second, I create two measures:

Maxdate each month = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Date].[Year], 'Table'[Date].[Month]
    )
)
Mindate each month = 
CALCULATE(
    MIN('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Date].[Year], 'Table'[Date].[Month]
    )
)

Third, I count the [service ID] with measure:

Active service = 
CALCULATE(
    COUNT([Service ID]),
    FILTER(
        ALLSELECTED(Sheet10),
        [Connected Date] <= [Mindate each month]
        &&
        [Disconnected Date] >= [Maxdate each month]
    )
)

The result:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.