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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors