Filter on the right row for each user based on timetravel

[bi_ninja]

Hi guys,

I have a mix of data coming from Google Analytics for web traffic which I want to join in with our own DWH to be able to get the best possible insights. So we have the following tables:

1 - Google Analytics - Traffic - It contains traffic on our website. The key here is user_id which can be joined with our DWH to find out more about this user/customer.

2 - Contact - SQL - It contains all our company customers/members in DWH. The key here is user_id to join with Google Analytics table. The table only contains there last status change.

3 - Contact History - SQL - This table contains historical changes for each customer/member in DWH. A customer can go from being a customer to becoming a member and later on go from being a member back to customer. For each change, there is a new row with a dbt_valid_from and dbt_valid_to date.

What I am looking for:

I want to count the number of distinct user_id in Google Analytics - Traffic for the selected dates in "Date" table and I would like to filter on the "active" row at that time. I want to make a line chart from Jan 2024 to September 2024. In this chart I would like to get the customer_type status for each user for each month. The dbt_valid_from and dbt_valid_to columns are the ones to tell when a row is valid or not. The slicer date must be between dbt_valid_from or dbt_valid_to date to select the correct. For current active status the dbt_valid_to can also be NULL as the rows is not expired yet.

I am also attaching a shot from my model. Thanks for all help in advance.

[Anonymous]

Hi @bi_ninja ,

Based on the description, try to create a measure to count the number of distinct user_id.

Active User Count = 
    VAR SelectedDate = SELECTEDVALUE('Date'[Date])
    RETURN
    CALCULATE(
        DISTINCTCOUNT('Google Analytics - Traffic'[user_id]),
        FILTER(
            'Contact History',
            ('Contact History'[dbt_valid_from] <= SelectedDate) && 
            (
                ISBLANK('Contact History'[dbt_valid_to]) || 
                ('Contact History'[dbt_valid_to] >= SelectedDate)
            )
        )
    )

Then, create a measure to get the customer_type status for each user.

Customer Type = 
    VAR SelectedDate = SELECTEDVALUE('Date'[Date])
    RETURN
    CALCULATE(
        VALUES('Contact History'[customer_type]),
        FILTER(
            'Contact History',
            ('Contact History'[dbt_valid_from] <= SelectedDate) && 
            (
                ISBLANK('Contact History'[dbt_valid_to]) || 
                ('Contact History'[dbt_valid_to] >= SelectedDate)
            )
        )
    )

Besides, drag the date field to the X-axis and drag the measure to the values.

Best Regards,

Wisdom Wu

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

[bi_ninja]

Thank you so much for your help. 

With the customer type measure, I cant use it as a legend on the line chart I am trying to make. What would be the solution for that?

I am basically looking for a visual like this. If a user is custom in January and turns member in July, I want it to be registered as a customer in January and member in July.