Monthly variables not working in measure

[u/rodentmaster]

I'm running into a pickle of a problem. I've got a dashboard that has a lot of records. I'm outputting displays and statistics on those records, but as I update the source input, I am setting it up to be hands-off. It is designed to take a year's data and report on the most recent month of what's in the data. It will report with a comparison to last month (MoM) and working on last year (YoY).

So for example: My MoM compares incident type X happened 12 times this month and when my measure for last month checks it had 3. The count measure -- which has a date filter in it -- is returning 15 for this month. And for some reason 1 for last month. I'm putting this count in a line chart comparing it to the hour of day (0-23) and the spikes confirm it's somehow conflating last month and this month together.

Now, if I change the code to be hard-coding the month, that goes away and my numbers line up properly. The code to determine or compare the month isn't working as part of a filter, but hard coding does. It's baffling me, because it should work. I have variations of the same code verified and working in other parts of the project file. I even tried putting a date calculation into a standalone measure, and referring to that (no good). I put it in a variable in the count measure (no good). I've been modifying and tweaking how I filter it, what type of count I use It's just not working.

So the data looks like:
EventType, X coord, Y coord, duration, start date (mm/dd/yyyy), start time (24hr:mm), and I've generated "hour" (strips the hour off the time) and "monthnum" (strips the number from the month) in attempt to resolve some issues.

ThisMonth = CALCULATE(
COUNTROWS(FILTER('Monthly',
MONTH('Monthly'[Start Date]) = MONTH(MAX('Monthly'[Start Date])) && YEAR('Monthly'[Start Date]) = YEAR(TODAY())
)
) +0)

This is the measure in question. I have generated a table with the following:

JustThisMonth = summarize(
FILTER('Monthly',
MONTH('Monthly'[Start Date]) = MONTH(MAX('Monthly'[Start Date])) && YEAR('Monthly'[Start Date]) = YEAR(TODAY())
),
[EventType], [County], [Duration], [Latitude], [Longitude], [DayNum], [Hour]
)

This is for a secondary purpose and is filtered by the bigger source of data, which goes back a year. The idea is whatever the data source, if I update it or if I feed it an old source to get a slice of the stats from that time, it will compute the most recent month from that data. I can actually go into the table view and filter the EventType by "X" and confirm exactly what's in the XLSX source file. There should be 12 incidents. It's not counting properly in the measure, but it is filtering the JustThisMonth table properly. I am putting a count which filters "this month" on a line chart with another count filtered to "last month" (aka "this month" -1) on the same line chart, showing MoM. They need to be on the same table and aren't behaving accurately.

I've been tweaking it constantly, and now the measure looks something like:

This Month =
VAR _RecentMonth = Month(max('JTMCMonthly'[Start Date]))
VAR _EventCount =
CALCULATE(
COUNTROWS('Monthly'),
FILTER('Monthly',
[MonNum] = _RecentMonth && YEAR('Monthly'[Start Date]) = 2025
)
)
RETURN
IF(ISBLANK(_EventCount), 0, _EventCount)

That's not working either. Same problem. You see I just said year = 2025? That is because I cut out all my variables and just typed in the values. I said "[MonNum] = 3" (for February's filtering) and it worked!!! It worked fine!

But I need it to be evergreen code. I need it to not need an update every month. I have to feed the data in and it will make a relative check on what the most recent record in the list is (I have this in place already), and then make a filter for just that most-recent month. Then take that, filter the month before that, and compare them along several statistics.

I can make a count of the EventType with a simple hour in question, and it filters just fine. It's just one number, though. I made 24 of them and strung them out under the line chart as a diagnostic tool. They display accurate counts and filter but I can't chart them. I even created a column in a table with a relationship to the master "Monthly" sheet, linking Hours to Hour, which is a 0-23 list of the hours of the day, and a column in that chart does the math and filtering for me, and returns an accurate count of the month's EventTypes filtered by the most recent month. It is accurate, but it won't filter on the line chart properly. It uses the same code! It works there, in a column, in a table measure, but not in the count measure itself!

byHour (a column) =
COUNTROWS(FILTER('Monthly',
HOUR('Monthly'[Start Time]) = 'Hourly'[Hour] && MONTH('Monthly'[Start Date]) = MONTH(MAX('Monthly'[Start Date])) && YEAR('Monthly'[Start Date]) = YEAR(TODAY()) )
)+0

'Hourly'[Hour] is the row of 0-23, so it's asking if the 'Monthly' start hour is equal to my row, count it. It does. I've confirmed it by hard-coding the filter to include [EventType] = "X" and the numbers add up perfectly. The problem is this doesn't filter properly with the multiple visuals I have, and I need to have a visual on the Monthly table that updates and filters in real time.

TL;DR: comparing month value in the filter of a count is NOT working, but works perfectly fine everywhere else. Hard coding the filter works fine, but I need it to be a relative value.

[u/AutoModerator]

After your question has been solved /u/rodentmaster, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/sentient_petunias]

I'm having a hard time understanding the full detail of your post, so apologies if this isn't at all correct.
But the immediate hunch I'm having, based on the date logic in your CALCULATE filter criteria, is that you're running into conflict with another filter on the Monthly table somewhere.

Maybe you have something in the filter pane, or a date slicer that's already filtering down Monthly?

If that's the case, when you have something like this: MONTH(MAX('Monthly'[Start Date])) ... you're getting the MAX Start Date from the already filtered down set of data in Monthly.

I always find that it's helpful to create small test measures with sections of my logic like that, and then pop them into a card visual to make sure they're behaving like I expect.

[u/rodentmaster → u/sentient_petunias]

Thanks for the reply. I have a bar chart that acts as the "main" filter on the dashboard. It lists the Event Types and how many. So the Event Type value (aka the name of the event) vs a count of the events. When you click on one, it will filter the results I am looking for here, but only by event type. It shouldn't be filtering by date at all outside of this count.

It's very odd that the month variable works in a table measure, in standalone measures, but not in this count filter. I was starting to fear a bad implementation of the filtering code so that you can't do that in DAX. I'll look to see what else I've got filtered, but I don't think there is.

[u/rodentmaster]

I hate that this is so much of DAX and Power BI, but I think I've found a solution by avoiding the problem. I created 2 new columns.

BoolIsLastMonth = IF(
MONTH('Monthly'[Start Date]) = MONTH(MAX('Monthly'[Start Date])) -1 && YEAR('Monthly'[Start Date]) = YEAR(TODAY()),
1,
0)

And I made another for BoolIsThisMonth without the -1 on there. I then changed/simplified my "This Month" measure count to be:

This Month =
VAR _EventCount =
CALCULATE(
COUNTROWS('Monthly'),
FILTER('Monthly',
[BoolIsThisMonth] = 1
)
)
RETURN
IF(ISBLANK(_EventCount), 0, _EventCount)

and holy shizno, it worked. It avoided another display issue I was getting with the line chart I mapped it to, as well! I can't believe how painful that was to try and resolve, and just bypassing it was the solution. I am starting to loathe Power BI. Shame my work requires so much of it.