Need help on calculating values based on the date slicer from another table

[cz37]

Hi Everyone,

I want to calculate % of users who had activities in a given period based on the activity date. The denominator would be all users who created their accounts before the end date of the selected activity date range. The numerator is number of users who had activities during the selected activity dates.

The Activity table stores all the user activities (one activity per row). The Users table includes all users. One user may have zero or multiple activities so it's many to one relationship. The end users of the report are expected to filter on the Activity Date via a slicer.

Below is some dummy data in those two tables. If report users select activity date range from Jul 1 to Sept 30, the % of users who had activities should be 3/4. User E is not included in the denominator because its account was created after Sept 30. I am not able to include the User D into the denominator because it did not have activity during the selected activity date period.

• Activity table

Users           Activity Date
A                 9/20/2021     

A                 8/21/2021 
B                 8/30/2021       
C                 7/3/2021      

• Users table

User    Date of Account Creation
A          6/2/2021
B          7/2/2021
C          5/11/2021

D          8/10/2021
E          10/10/2021

Could someone help me on this? Much appreciated!

[ERD]

Hi @cz37 ,

You can try this measure (supposing you have a Date table connected to your tables):

activity% =
VAR minDate = MINX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] )
VAR maxDate = MAXX ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] )
VAR usersAmt =
    CALCULATE (
        COUNTAX (
            FILTER ( 'T-2', 'T-2'[Date of Account Creation] <= maxDate ),
            'T-2'[User]
        ),
        ALL ( 'Date'[Date] )
    )
VAR activeUsers = COUNTAX ( DISTINCT ( 'T-1'[User] ), 'T-1'[User] )
RETURN
    activeUsers / usersAmt

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