Forum Discussion
Active Users between Date Ranges, each Month
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
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
4 Replies
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], ", " )Hi Anonymous
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.
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
Anonymous
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
