Calculate active users within a timeframe

[galgadot10]

Hello all! I've been stuck on this one for a while and could use some help please! As a gym owner, I have a table of gym sessions per member:

Member	Dates visited
Anna	19/03/2023
Anna	28/05/2023
Mark	27/04/2023

I would like to count members over this 3 month period, and Anna SHOULD be counted as a member in April even though she didn't have any active sessions in April. How do I do this? When I use my slicer with a COUNT measure currently, Anna isn't counted as active during this period. I should ideally see that March has 1 member, while April and May have 2 members. Is there any way to achieve this please?

[Atif2917]

To count members who have had active sessions within a specific 3-month period and exclude those who haven't had any active sessions in that period, you can use a combination of functions like COUNTIFS, SUMPRODUCT, and date calculations in Excel or Google Sheets. Here's a step-by-step guide on how to do it:

Create a List of Members: In a new column, list all the unique member names.

Determine the Start and End Dates for the 3-Month Period:

Let's say you want to count members for the period starting from March 1, 2023, to May 31, 2023.
Count Active Members for the Period:

In another cell, you can use the following formula (assuming the member names are in column A and the dates in column B):
ruby
Copy code
=SUMPRODUCT((A2:A100="Anna")*(B2:B100>=DATE(2023,3,1))*(B2:B100<=DATE(2023,5,31)))
Replace "Anna" with the cell reference that contains the member name you want to count. This formula checks if the member name is "Anna," and if the corresponding date is within the specified period. Adjust the date ranges accordingly.
Repeat for Other Members:

To count other members, simply change the member name in the formula and drag it down for each member you want to count.
Sum the Counts:

Finally, sum the counts for all members to get the total count for members who had active sessions within the specified 3-month period.
Here's a breakdown of the formula:

(A2:A100="Anna") checks if the name in each row is "Anna."
(B2:B100>=DATE(2023,3,1)) checks if the date is greater than or equal to March 1, 2023.
(B2:B100<=DATE(2023,5,31)) checks if the date is less than or equal to May 31, 2023.
SUMPRODUCT sums the results of these conditions for each row.
This formula counts members who had active sessions within the specified 3-month period and excludes those who didn't have any active sessions during that time. You can apply it to all members in your list to get the total count for the entire period.

[galgadot10]

Hi I've updated the description to be a little clearer and I hope that helps. I'm ideally looking for a more automated solution to work on json file with thousands of members so anything less manual would really help me out! 

Also, I'd like Anna counted as an active member even when she hasn't had a session, because she's still registered with us. Do you have an alternative solution I could use in Power Bi please?

[Anonymous · Accepted solution]

Hi @galgadot10 ,

I suggest you to create a calendar table to help your calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM"),"MonthSort",MONTH([Date]))

Data model:

Measure:

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Member] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Dates visited] <= MAX ( 'Calendar'[Date] )
            && EOMONTH ( 'Table'[Dates visited], 3 ) >= MAX ( 'Calendar'[Date] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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