Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ry5
Helper I
Helper I

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

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.

Ry5_0-1658375863817.png


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

1 ACCEPTED SOLUTION
Ry5
Helper I
Helper I

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

View solution in original post

7 REPLIES 7
Ry5
Helper I
Helper I

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

Ry5
Helper I
Helper I

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.

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

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

amitchandak
Super User
Super User

@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)  ))

 

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

@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) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors