Active Users between Date Ranges, each Month

[Mark_Holland_GD]

Hi,

I have a list of Customers with Start and End Dates for a program. Here's some example data:

Customer ID	Start Date	End Date
A	10/09/2024
B	01/06/2024	05/08/2024
C	03/10/2024	05/11/2024
D	08/08/2024
E	24/05/2024	25/10/2024
F	01/11/2024
G	23/09/2024

I want to have a chart that shows the total number of Active Users each month based on these dates. An Active User must have a Start Date that is greater than or equal to the reporting month start date, and either have a blank End Date or and End Date this is less than or equal to the reporting month end date. 

So the table above would show the following results:

Reporting Month	Active Users
August 2024	3 (B, D & E)
September 2024	4 (A, D, E & G)
October 2024	5 (A, C, D, E, & G)
November 2024	5 (A, C, D, F & G)

Any thoughts on how to do this?

Thanks,

Mark

[Kedar_Pande]

@Mark_Holland_GD 

Create a Date Table:

DateTable = 
ADDCOLUMNS (
CALENDAR (DATE(2024, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthYear", FORMAT([Date], "YYYY-MM")
)

Create a measure:

Active Users = 
VAR StartOfMonth = STARTOFMONTH(DateTable[Date])
VAR EndOfMonth = ENDOFMONTH(DateTable[Date])

RETURN
CALCULATE (
COUNTROWS(Customers),
FILTER (
Customers,
Customers[Start Date] <= EndOfMonth &&
(
ISBLANK(Customers[End Date]) || Customers[End Date] >= StartOfMonth
)
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[danextian]

Hi @Mark_Holland_GD 

I answered a similar post a few days ago. In the attached file, you will see a measure that uses a disconnected table and one that has a relationship to start date.

[Mark_Holland_GD · Accepted solution]

Hi @danextian,

We're almost there. I made a couple of small amendments to the DAX and it's now working:

Total Puppies in Training = CALCULATE([Total Dog Codes], 
CONTAINSSTRING('PROGRAM ENGAGEMENT'[Dog Program Classification], "C2"),
FILTER(SUMMARIZE('PROGRAM ENGAGEMENT', 'PROGRAM ENGAGEMENT'[Program Start Date], 'PROGRAM ENGAGEMENT'[Program End Date]),
'PROGRAM ENGAGEMENT'[Program Start Date] <= MAX('02: Calendar'[Dates]) && ( 'PROGRAM ENGAGEMENT'[Program End Date] > MAX( '02: Calendar'[Dates]) || ISBLANK('PROGRAM ENGAGEMENT'[Program End Date]))))

In my example, I'm looking to count the number of Puppies in our training program, which is the classification filter in this DAX.

Thanks,

Mark

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to use calendar dimension table.

Active customers count: = 
VAR _t =
    FILTER (
        customer,
        customer[Start Date] <= MAX ( 'calendar'[Date] )
            && OR (
                ISBLANK ( customer[End Date] ),
                customer[End Date] >= MIN ( 'calendar'[Date] )
            )
    )
RETURN
    COUNTROWS ( _t )

Active customers list: = 
VAR _t =
    FILTER (
        customer,
        customer[Start Date] <= MAX ( 'calendar'[Date] )
            && OR (
                ISBLANK ( customer[End Date] ),
                customer[End Date] >= MIN ( 'calendar'[Date] )
            )
    )
RETURN
    CONCATENATEX ( _t, customer[Customer ID], ", " )