Record count using calendar date table

[esuing]

Hello everyone. I've been struggling with this for a while and wondering if someone can help. I'm trying to create a table that will return a count of active producers each month based on two date fields: Prod First Ship Date and Prod Term Date.  Here's a look at a sample of the table:

I would like my final table to show active producers on the last day of each month. To be consider active, a producer would have [Prod First Pickup Date] <= #date# AND (Prod Term Date is Null or Prod Term Date > #date#)

I do have a calendar table joined to each of the date fields and they have all dates included in them. Ultimately, I'm looking for something like this:

I can show either Month or Last Day of Month. Don't really care about that. I was able to get the last day of the month using this formula:

Max First Ship Date = CALCULATE(max('Prod First Ship Date Calendar'[Prod First Ship Date]),CROSSFILTER(Producer[Prod First Ship Date],'Prod First Ship Date Calendar'[Prod First Ship Date],None))

But everything I've tried since then to get a count of the number of active producers has failed. The numbers I got in the table now came from this formula, but it's only checking against the First Pickup Date and doens't take Term Date into account at all:

Member Count C = CALCULATE(COUNTROWS(Producer),FILTER(ALL('Prod First Ship Date Calendar'),'Prod First Ship Date Calendar'[Prod First Ship Date]<=MAX('Prod First Ship Date Calendar'[Prod First Ship Date])))

Any help would be greatly appreciated! 

Thanks!

Eric

[d_gosbell · Accepted solution]

If you want to do a "between" style of join like this you can't really use relationships (in fact they will cause issues). So you could either remove them entirely or use the CROSSFILTER() function to switch them off for this measure as I have done below. A pattern like the following should give you what you want

Active Producers =
CALCULATE (
    COUNTROWS (
        FILTER (
            Producer,
            Producer[Prod First Ship Date] <= MAX ( 'Prod First Ship Date Calendar'[Prod First Ship Date] )
                && (
                    ISBLANK ( Producer[Prod Term Date] )
                        || Producer[Prod Term Date] > MAX ( 'Prod First Ship Date Calendar'[Prod First Ship Date] )
                )
        )
    ),
    CROSSFILTER ( Producer[Prod First Ship Date], 'Prod First Ship Date Calendar'[Prod First Ship Date], NONE )
)

[esuing]

This worked great! As a beginner, I knew I need to use the CROSSFILTER function within my CALCULATE but didn't know where it made sense (inside the FILTER or not). 

Thanks!

Eric