cancel
Showing results for
Did you mean:
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!
Resident Rockstar

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.

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors