Measure that filters based on create date to unrelated calendar date

[lorasloan]

I have a data table and a calendar table with no relationship.  I need to create different measures based on different dates from data table (e.g. Count(id) where open date=calendar date, Count(id) where closed date= calendar date.  

I'm using calendar date as my base column in table.   I've tried calculate and countx(filter... and it finds the first date of one table but when I select equal '=' (to get the second date) it won't show up.  

I've tried using 

OpenReq = var caldates=DISTINCT('Calendar'[MonthYYMM]) return CALCULATE(vw_Overall_Scorecard_RICs[OpenRICs],FILTER(vw_Overall_Scorecard_RICs,vw_Overall_Scorecard_RICs[OpenDateYYMM] in caldate ))

but I get no values.  I've verified both date formats are the same.  

[AlexisOlson]

The caldate inside the FILTER doesn't match the variable caldates. I'd also expect [OpenRICs] to be wrapped in a COUNT or DISTINCTCOUNT function rather than having a raw column reference.

[lorasloan]

the caldates/caldate was a typo on my part.  They are the same.  OpenRICs is already a measure with a count.  

[AlexisOlson]

Got it. FYI, general practice is not to use table names with measures to avoid this sort of confusion (and because it doesn't make much difference what table they're stored on).

How is [OpenRICs] defined?

[lorasloan]

OpenRICs = count(vw_Overall_Scorecard_RICs[ric_pk])

Thanks

[v-easonf-msft · Accepted solution]

Hi, @lorasloan 

I did not find any errors in your formula. I suspect this has something to do with your table structure

Can you show screenshots of your original data of the two tables?

You can also try the following formula：

OpenReq2 =
    CALCULATE (
        vw_Overall_Scorecard_RICs[OpenRICs],
        FILTER (
            vw_Overall_Scorecard_RICs,
            vw_Overall_Scorecard_RICs[OpenDateYYMM] IN VALUES ( 'Calendar'[MonthYYMM] )
        )
    )

Best Regards,
Community Support Team _ Eason

[lorasloan]

@v-easonf-msft 

I ended up going a different direction using an active relationship from table to calendar for opendate and inactive relationship with closeddate, then used function userrelationship for closeddate measure.  

I did create a new report to test out code and both my original code and yours worked.   I think something was wrong with the value 'Calendar'[MonthYYMM] in my original report.  

Thank you for followup.