Calculated Column with Date Limits

[arhomberg]

Hello all,

I am attempting to create a calculated column that does a distinct count within another table. I have a subscriptions table that holds a customer id and a start and end date. There is a second table, usage, that holds the customer id, a user id, and a date for when an action was completed. I am trying to set up a column in the subscriptions table that uses the customer id in each table, filters the usage table to be greater than the start date but less than the end date in the subscriptions table, and does a distinct count of the user id’s.

Subscriptions

Usage

Result

For customer 123-456, 2 would be returned because user 'abc' and 'def' both had a record with a date between 1/1/2022 and 1/1/2023. For customer 789-101, 2 is returned because only 2 distinct users had a record in the time frame of 6/1/2022 - 6/1/2023. User 'zxy' was not within this time frame.

Any assistance with this would be greatly appreciated, thanks!

[arhomberg · Accepted solution]

I was able to solve this with the following:

[Anonymous]

1. Use the related function to add your start and end date into your subscriptions table.

2. Create a new calculated column in your subscriptions column to check if the salesdate is between the start and end. like this: 

Sales Within Range = IF(AND([date] >= [start_date], [date] <= [end_date]), 1, 0)
3. Then create a measure to distinctcount the userid where sales within range equals 1.

Distinct Users Within Range =

CALCULATE(

    DISTINCTCOUNT('Usage'[user_id]),

    'Usage'[Sales Within Range] = 1

)

[ChrisMendoza · Accepted solution]

@arhomberg - This seems to work:

Column =
SUMMARIZE (
    FILTER (
        Usage,
        Usage[customer_id] = Subscriptions[customer_id]
            && Usage[date] >= Subscriptions[start_date]
            && Usage[date] <= Subscriptions[end_date]
    ),
    "@", DISTINCTCOUNT ( Usage[user_id] )
)

[arhomberg]

Awesome, thanks @ChrisMendoza!

[arhomberg · Accepted solution]

I was able to solve this with the following: