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.
Solved! Go to Solution.
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.
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])
Community Support Team _ Eason
@Ry5 , In you date table have a column YYYYMM
Year month = year([Date])*100 + Month([Date])
That will correct value
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
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 , 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)
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!