cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cz37
Frequent Visitor

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

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!
1 REPLY 1
ERD
Memorable Member
Memorable Member

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

ERD_0-1632327060724.png

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors