Count users between start and end date by month

[ruanolin]

Hello,
I am performing user counting by company and group.
I have the following tables:

With the following tutorial DAX measure with start AND end date for Power BI and with the following DAX formula, I am able to have the user count by company, group and day.

9 Users currently per groups =
CALCULATE (
    COUNTROWS ( Users ),
    FILTER (
        VALUES ( GroupMembers[startDate] ),
        GroupMembers[startDate] <= MIN ( 'Date'[Dates] )
    ),
    FILTER (
        VALUES ( GroupMembers[endDate] ),
        OR (
            GroupMembers[endDate] = DATE ( 9999, 12, 31 ),
            GroupMembers[endDate] > MAX ( 'Date'[Dates] )
        )
    ),
    FILTER (
        VALUES ( Users[startDate] ),
        Users[startDate] <= MIN ( 'Date'[Dates] )
    ),
    FILTER (
        VALUES ( Users[endDate] ),
        OR (
            Users[endDate] = DATE ( 9999, 12, 31 ),
            Users[endDate] > MAX ( 'Date'[Dates] )
        )
    )
)

The problem is that I want to perform the same count by month instead of days.
When I drill up for the date table, I get the following wrong result:

I would like to obtain the following result (if a user is in the group one day in a month, I want to count it)

	August	September
E1 - Interns	1	3
E1 - Externs	0	3
E2 - Interns	1	2
E2 - Externs	1	2

I attach the sample file

[lbendlin]

some general comments

- you may want to spend some time cleaning up your data model

- try not to use auto date time hierarchies

- DO NOT use dates like 9999-12-31  - they are adding an enormous storage overhead and performance impact.  Keep End Dates as BLANK() when they are undefined, and use COALESCE([End Date], TODAY())  to get a meaningful date.