Count rows where Month equals the earliest selected month in date slicer

[Ry5]

Hello,

I have a date slicer set to BETWEEN and a table with two fields - a date field and an ID field.
I need a measure which counts the number of ID's where the month of the incident date matches the month of the earliest date selected in the date slicer.
I also need to do the same for the latest selected month.
In the example below, the earliest selected date is in February 2022 and the latest is in June 2022.

There is also a seperate created date table with the fields Date, Month, Month Number, and Year.

The closest I have been able to get is 
EarliestMonthCount = CALCULATE( DISTINCTCOUNT( [ReferenceID]),
'Created Date Table'[Month] = MIN( 'Created Date Table'[Month])

But this returns the count for April, as it's picking up the month names alphabetically.

Thank you

[Ry5 · Accepted solution]

I no longer require an answer to this question, but am unable to mark the question to be closed

[Ry5]

Thank you for trying, however neither of these work

The MIN[Year Month] returns the earliest month and year in all of the data not the earliest selected in the slicer. So when using it in the original measure it returns blanks (due to that month being filtered out)

The second option using variables also returns blanks, and I don't fully understand it so cannot adequately problem solve it.
It looks like it's counting all the ReferenceID's which occured on the the last date of the last selected month and then minusing the count of all the ReferenceID's which occured on the the last date of the first selected month. Which is not what I'm after, although I could have interpreted this completely wrong.

[v-easonf-msft]

Hi, @Ry5 

Is it possible to use 'Created Date Table' [month number] instead of field 'Created Date Table' [month] ?

EarliestMonthCount = CALCULATE( DISTINCTCOUNT( [ReferenceID]),
'Created Date Table'[Month Number] = MIN( 'Created Date Table'[Month Number])

Best Regards,
Community Support Team _ Eason

[Ry5]

No, I tried this but it says there's something wrong with the relationship between the tables

[amitchandak]

@Ry5 , In you date table have a column YYYYMM

Year month = year([Date])*100 + Month([Date])

That will correct value

Try like

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return

calculate( sum(Table[Value]), filter('Table', eomonth('Table'[Date],0) = eomonth(_max,0)  )) -
calculate( sum(Table[Value]), filter('Table', eomonth('Table'[Date],0) = eomonth(_min,0)  ))

[Ry5]

Thank you, where in the new measure do I use the Year Month column?

[amitchandak]

@Ry5 , That is a suggestion for your code. In case my code you do not need that.

I used the date itself to get the min month

eomonth(_min,0)  is the last date of the month and I am comparing it with eomonth(Date[Date],0)