Forum Discussion

galgadot10's avatar
galgadot10
Regular Visitor
2 years ago
Solved

Calculate active users within a timeframe

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Atif2917's avatar
Atif2917
New Member
2 years ago

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's avatar
    galgadot10
    Regular Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.