Counting dates that fall between date range in another table (M to M relationship)

[akmiller]

I have a flag or measure REMCUT which shows if an open date from one table occurs between a date range in another table for the same account. My issue is that I would like to count the number of service orders that occur in a given month with the REMCUT flag but my bar chart goes blank when I add it. 

REMCUT = 
CALCULATE(COUNT(RemoteCutoff[BI_SO_NBR]), 
FILTER(RemoteCutoff,
RemoteCutoff[BI_ACCT] = SELECTEDVALUE(PPBilling[BI_ACCT])&&
RemoteCutoff[BI_OPEN_DT] >= SELECTEDVALUE(PPBilling[BI_BCH_DT])&&
RemoteCutoff[BI_OPEN_DT] <= SELECTEDVALUE(PPBilling[EndDate])&&
SELECTEDVALUE(PPBilling[BI_TRAN_ACTIVITY]) = "ADD"))

Below you can see the REMCUT flags 12 service orders for this account which occured between BI_BCH_DT and EndDate. In the table below that, service order 2676574 does not get flagged because it occurs outside of the range. 

How can I get the count of service orders to show by the month they were opened?

The two tables are joined on a many to many relationship between accounts if that matters. 

Below is a link to sample file

https://www.dropbox.com/s/odz4cw4dztov4nd/Page_Test.pbix?dl=0

Thank you for your help!

[Anonymous · Accepted solution]

Hi @akmiller ,

I'm sorry I don't understand your needs very clearly, is a given month given between two dates in another table or in some other way? Can you provide me with the expected results you want in the form of a table.

You can refer to the following documents that may be helpful to you:

Solved: Check if record from table A falls within a date r... - Microsoft Power BI Community

Solved: Count items in table A if filter date from table B... - Microsoft Power BI Community

Solved: DAX for counting overlapping date ranges across mu... - Microsoft Power BI Community

Solved: Count Rows Falling between two dates ( from discon... - Microsoft Power BI Community

Best Regards,

Neeko Tang

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

[akmiller]

Thank you, the second link (https://community.powerbi.com/t5/Desktop/Count-items-in-table-A-if-filter-date-from-table-B-falls-be...) and specifically this code:

Serial Number in TB 1 =
VAR t =
    FILTER (
        TableA,
        TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] )
            && TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] )
    )
RETURN
    COUNTROWS ( FILTER ( t, [Serial Number] IN VALUES ( TableB[Serial Number] ) ) )

Got me where I needed to be. Thanks for your help!