Count Transactions If Transaction's Date Range Fell Within Calendar Month

[paulsnet1986]

I have two tables: a table with customers active in a calendar month and a table of transactions with start and end dates. I want to make a Power BI table that lists the customers and, when filtered by a calendar month, counts the number of transactions that were running during any part of the calendar month. I cannot think of how to do this because I need to link the tables by both customer number and dates? Any help would be appreciated!

Table 1

Calendar Month	Customer ID
3/1/2023	1111
3/1/2023	2222
3/1/2023	3333
3/1/2023	4444
4/1/2023	2222
4/1/2023	3333
4/1/2023	4444
4/1/2023	5555
4/1/2023	6666

Table 2

Transaction	Customer	Start Date	End Date
1	2222	1/1/2023
2	2222	1/15/2023	3/15/2025
3	2222	1/18/2023	2/27/2023
4	3333	3/12/2023	6/11/2023
5	3333	4/10/2023
6	1111	1/1/2023

 Desired Result:

Selected Month Filter: March 2023

Customer	Transaction Count
1111	1
2222	2
3333	1
4444	0

[Ashish_Mathur]

Hi,

What is the use of Calendar Month name column in Table1?  Also, for 2222, why should the answer be 2.  In March 2023, that Customer appeared only once.

[ThxAlot]

txn cnt.pbix

[Deepak_22]

Hello @paulsnet1986 ,To achieve the desired result follow the below steps.

Load Data:

Load both Table 1 and Table 2 into Power BI.

Relationships:

Navigate to the "Model" view.

Create a relationship between Table 1and  Table 2 based on the Customer ID column.

New Measure:

In Table 1, create a new measure for counting the number of transactions. The logic should check if the transaction's start and end dates fall within the selected calendar month.

Here's a possible DAX formula for the measure

Transaction Count = 
COUNTROWS(
    FILTER(
        'Table 2',
        'Table 2'[Start Date] <= MAX('Table 1'[Calendar Month]) &&
        OR(
            ISBLANK('Table 2'[End Date]),
            'Table 2'[End Date] >= MIN('Table 1'[Calendar Month])
        )
    )
)

Visualization:

Go to the "Report" view.

Drag and drop a table visualization into the report canvas.

Add Customer IDfrom Table 1 and the Transaction Count measure to the table.

Apply a slicer visualization for the Calendar Month from Table 1 to filter by month.

This solution will provide a table that lists customers and, when filtered by a calendar month, counts the number of transactions that were running during any part of that calendar month

If you find this helpful, please provide a kudo and mark it as an accepted solution.