DISTINCTCOUNT with conditions across several tables

[Paulo84]

Hi All, 

Was wondering if you may be able to assit with a scenario that has me stumped.

I have 3 x tables in my model:

* A 'Key Date' Table with a column of dates

* A 'Transactions' table with a unique Client ID, transaction date and transaction amount 

* A 'Client Details' table linked to the 'Transactions' table through unique Client ID

I'm then trying to create a calculated column on the 'Key Date' table which will count the number of clients who meet 2 criteria:

* Client has a 'date became client' within 4 months of key date; and

* Client has not made a transaction as of the 'key date'

Another scenario I'm trying to determine is a count of clients who have made a transaction within 13 months of each key date. 

Any assistance is greatly appreciated!

[v-jayw-msft · Accepted solution]

Hi @Paulo84 ,

Check the formula.

Column 3 =
CALCULATE (
    DISTINCTCOUNT ( 'Client Details'[Client ID] ),
    FILTER (
        'Client Details',
        'Client Details'[date became client] >= EDATE ( 'Key date'[date], -3 )
            && 'Client Details'[date became client] <= 'Key date'[date]
    )
)
    - CALCULATE (
        DISTINCTCOUNT ( 'Client Details'[Client ID] ),
        FILTER ( Transactions, Transactions[transaction date] = 'Key date'[date] )
    )

Here's my sample data and result.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[v-jayw-msft]

Hi @Paulo84 ,

I'm not sure what is "date became client" in your model. Is it transaction date or another column in Client Details table?

Best Regards,

Jay

[Paulo84]

Hi @v-jayw-msft ,

'Date became client' is not a transactional date - Think of it as the date they 'signed on' to become a client (this date will be prior to transacting).

It is another column in the client details table.

[MFelix]

Hi @Paulo84 ,

Can you please share same sample data and expected result ,please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

Based on your data the results can vary, so if you provide a sample data it's easier to get the result you need.