Dates & Calculate - Filter Where Dates in one Table appear in Calendar Table

[Mark_Holland_GD]

Hi,

I have one Table of data containing a Client Code and an First Gift Date field:

First Gift Date      Contact Client Code

02/07/2024         C220964

01/08/2024         C276498

05/08/2024         C256879

21/10/2024         C256687

I also have a Calendar Table in my report, so I can link other Tables together for filtering.

I want to calculate the distinct count of Client Codes where the First Gift Date matches a Calendar Date, so I can see how many Clients first gave a gift in each Month. So with the above Data, there would be 1 in July, 2 in August, 0 in September and 1 in October.

I'm not sure how to set up the CALCULATE function to lookup Dates in my Data Table with Dates in my Calendar Table.

Any thoughts on how to do this?

Thanks,

Mark 

[Anonymous · Accepted solution]

Hi @Mark_Holland_GD 

Thank you very much Irwan for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

“Customers”

“Opportunities”

“Calendar”

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN(Customers[First Gift Date]), MAX(Customers[First Gift Date])),
    "Month Name", FORMAT([Date], "MMMM")
)

Create a measure.

Distinct Customers First Gift = 
    CALCULATE(
        DISTINCTCOUNT('Customers'[Customer ID]),
        USERELATIONSHIP('Opportunities'[Opportunity Date], 'Customers'[First Gift Date]),
        USERELATIONSHIP('Opportunities'[Opportunity Date], 'Calendar'[Date])
    )

Here is the result.

If you still have questions, please provide sample data that adequately covers your question and the expected results based on the sample data you provided.

Regards,

Nono Chen

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

[Irwan]

hello @Mark_Holland_GD 

since you want to show in month name, then you need to create a calculated column for month name from your fact table (you can do with measure depend on your goal).

the easiest way is using table visual, and choose distinct count.

the other way is using SUMMARIZE.

Hope this will help.

Thank you.

[Mark_Holland_GD]

Hi @Irwan ,

I should have been clearer. There are over 200,000 rows of data - the numbers I gave above were just to illustrate the set up.

My Calendar table has Month Name that I need and I don't want to summarize a table just for this solution.

I want to find a way to use CALCULATE to filter the Distinct Count to look at my table, see if it matches a Date on the calendar tables, and return the values that sit in that bucket.

Does that make sense?

[Irwan]

hello @Mark_Holland_GD 

if you only want to show the number, then using table visual is again the easiest way. esspecially as you mentioned, there is a relationship between calendar table and fact table

otherwise, simple DAX measure should do.

Distinct = DISTINCTCOUNT('Table'[Contact Client Code])

Hope this will help.

Thank you.

[Mark_Holland_GD]

Hi @Irwan ,

It's not that simple I'm afraid. Let me try again.

• I have 3 data tables - one for Customers, one for Opportunities and one is a Calendar Table
• The Calendar Table links to the Customer and Opportunites tables on multiple date fields - I've made these inactive and will use USERELATIONSHIP to filter
• The Customer Table has a unique list of Users with one field called First Gift Date. This is the first time that user has donated
• The Opportunities table has all kinds of ways someone can give money. It links to the Customer Table via an ID. There are lots of fields on the Opportunites Table that are needed for filtering and different breakdowns
• I want to do a DISTINCT COUNT of CUSTOMER IDS where the OPPORTUNITY was their First Gift Date, linked to the CALENDAR Table

Does that make sense?