Forum Discussion

esuing's avatar
esuing
Helper I
7 years ago
Solved

Record count using calendar date table

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 fi...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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 )
)
d_gosbell's avatar
d_gosbell
Super User
7 years ago

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's avatar
    esuing
    Helper I
    7 years ago

    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