Forum Discussion

arhomberg's avatar
arhomberg
Helper I
3 years ago
Solved

Calculated Column with Date Limits

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 ...
Need Help
  • arhomberg's avatar
    arhomberg
    3 years ago

    I was able to solve this with the following:

     

  • ChrisMendoza's avatar
    ChrisMendoza
    3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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
)