% of Months within given range in which at least two dates can be found within a column

[AloisAlzheimer]

I have a table of dates of contact to various entities. The expectation is that each entity should be contacted at least twice per month starting with a given month. I'd like to report the number of months that each entity has been contacted per this expectation. My data look like this, with a relationship between Contact MonthYear and Date MonthYear. For Entity A, I would want for the measure to return 2, showing that only in two months thus far has the entity been contacted at least twice. Similarly, I would want B to return 2 as well. I imagine that COUNTX is involved, but I can't wrap my head around it! Any help would be much appreciated, as well as good search terms that would have led me in the right direction on my own.

Table of Contacts

Entity	Contact Date	Contact MonthYear
A	05-Apr-19	01-Apr-19
A	28-Apr-19	01-Apr-19
A	15-May-19	01-May-19
A	10-Jun-19	01-Jun-19
A	21-Jun-19	01-Jun-19
B	06-Apr-19	01-Apr-19
B	15-Apr-19	01-Apr-19
B	24-Apr-19	01-Apr-19
B	05-Jun-19	01-Jun-19
B	16-Jun-19	01-Jun-19
B	22-Jun-19	01-Jun-19

Calendar Table

Date MonthYear

01-Mar-19

01-Apr-19

01-May-19

01-Jun-19

01-Jul-19

01-Aug-19

[Jihwan_Kim · Accepted solution]

Hi, @AloisAlzheimer 

Please check the below picture and the sample pbix file's link down below.

Expected Result Measure =

IF (

ISFILTERED ( 'Table'[Entity] ),

COUNTROWS (

FILTER (

ADDCOLUMNS (

SUMMARIZE ( 'Table', 'Table'[Contact MonthYear] ),

"@contactcount", CALCULATE ( COUNTROWS ( 'Table' ) )

),

[@contactcount] >= 2

)

)

)

https://www.dropbox.com/s/ef697zun7vtndkw/alois.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[AloisAlzheimer]

Thank you so much, Jihwan. This worked great.

I have a *-1 relationship between Entity in this table and Entity in a fact table where there are a few other useful fields, like an Entity Location column. I've removed the IF(IS FILTERED()) bit, and it works fine when I use the Entity field from the fact table within my visual, but the figures are not calculated properly when I add the Entity Location column (they all show as 100%). Could you please help me understand where this is going wrong?

[Jihwan_Kim]

Hi, @AloisAlzheimer 

Thank you for your feedback.

I am not sure whether I understand your question correctly, but it seems like it has to fix a measure a little bit based on the direction of the two tables.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.

[AloisAlzheimer]

HI Jihwan,

I apologize for the delay in getting back to you. I don't want to bother you with anything further as you've already been so helpful. I've worked around this issue by adding this additional layer of analysis in the Excel file to which I'm exporting from Power BI.