Filter on the right row for each user based on the timeframe

[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.

This is the DAX I have tried so far but it is not giving me the correct row:

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)
            )
        )
    )

What I am looking to visualise:

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

Thanks in advance for all help.

[bhanu_gautam]

@bi_ninja , Try using below DAX and steps

DAX query:

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) &&
(

To create the line chart from January 2024 to September 2024, follow these steps:

Create a Date Table: Ensure you have a date table that spans the range from January 2024 to September 2024.
Join Tables: Make sure the Google Analytics - Traffic table is joined with the Contact table on user_id, and the Contact table is joined with the Contact History table on user_id.
Create Measures: Use the DAX measure provided above to count the active users for each month.
Create Line Chart: Use the date from the Date table on the X-axis and the measure Active User Count on the Y-axis.

[bi_ninja]

Thanks for your reply.

Looks like the DAX you wrote was cut before end?

Anyways, it looks pretty the same as the one I already have? The main point is that I need the correct customers status which is on the correct row based on time aswell in the line chart. That is not syncing so well for me.

[v-zhouwen-msft]

Hi bhanu_gautam ,thanks for the quick reply, I'll add more.

Hi @bi_ninja ,

What is this field? Can you provide some simple data of this table?

Assume the following data:
'From': 2/1/2024
'To': 2/15/2024
'Type': member
'From': 2/16/2024
'To': 3/1/2024
'Type': customer

Is this your expected result?
Feb: member = 2 customer = 1
Mar: member = 0 customer = 1

Is the date dimension table only used to filter users who logged into the website within the selected date?

Best Regards,
Wenbin Zhou