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.

     

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.