Accumulate sum minus abandoned

[ILAN102010SH]

Hello 🙂
I have table of 3 columns: user id, start date of service, end date
I need to show by year&month the neto of users. If
I succeeded in making accumulated sum for the users, but I need to add minus of the abandoned users.
Please help me. How can I add to the function a minus of the abandoned users correctly.

[v-chuncz-msft · Accepted solution]

@ILAN102010SH,

You may refer to the following DAX that creates a new table.

Table =
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDARAUTO (),
            "year", YEAR ( [Date] ),
            "month", MONTH ( [Date] )
        )
    ),
    "count", CALCULATE (
        DISTINCTCOUNT ( Table1[user id] ),
        FILTER (
            Table1,
            Table1[start date]
                < DATE ( [year], [month] + 1, 1 )
                && (
                    Table1[end date] >= DATE ( [year], [month], 1 )
                        || ISBLANK ( Table1[end date] )
                )
        )
    )
)