Count Number of Transactions If 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

   

Table Two

Desired Output

[v-zhangti · Accepted solution]

Hi, @paulsnet1986 

You can try the following methods.
Measure:

Count = 
VAR _selectedmonth = MONTH ( SELECTEDVALUE ( Table1[Calender Month] ) )
VAR _Count1 = CALCULATE ( COUNT ( Table2[Transaction] ), FILTER ( ALL ( Table2 ),
            MONTH ( [Start Date] ) <= _selectedmonth && [End Date] = BLANK ()
                && [Customer] = SELECTEDVALUE ( Table1[Active Customers] ) ) )
VAR _Count2 = CALCULATE ( COUNT ( Table2[Transaction] ), FILTER ( ALL ( Table2 ),
            MONTH ( [Start Date] ) <= _selectedmonth && MONTH ( [End Date] ) >= _selectedmonth
                && [Customer] = SELECTEDVALUE ( Table1[Active Customers] ) ) )
RETURN
    _Count1 + _Count2 + 0

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[vanessafvg]

please provide data in text format.