Forum Discussion
Monthly Count not working
I have the following measure that works perfectly fine in a clustered columns chart as it shows me the current month total (597)
ISINSCOPE New Cases =
IF(
ISINSCOPE(Date2[Mth]), -- Row Level (month) in scope so normal YTD logic
TOTALYTD(
COUNT('Cases'[Case Number]),
'Cases'[Created On]
),
-- Total level (month is not in scope so each visible mth is iterated)
SUMX(
VALUES(Date2[Mth]),
TOTALYTD(
COUNT('Cases'[Case Number]),
'Cases'[Created On]
)
)
)
=
However, when I logged on this morning my Card visual showed -- instead of 597
I've tried numerous measures that just simply try to COUNT case numbers where the created date is Current Month but they all give me the same result.
Is this because my Data Model refreshes at 0100hrs on 1st July and therefore the 597 is lost?
If so, apart from manually adjusting the 'Created On' filter in the Filter pane each month to show Cases Created on or after 1st Jun and On & Before 30th June, is there a measure that I could use that gives me this number that doesn't reset after midnight on the last day of the month? I'm guessing a sort of IF statement that counts the Cases created within the current month that doesn't reset after midnight on the last day?
Unfortunately, I cannot share a pbix file because of data confidentiality
Ps These measures all give me the -- for June:
New this month =
CALCULATE (
COUNT ( 'Cases'[Case Number] ),
DATESBETWEEN ( Date2[Date], EOMONTH ( TODAY (), -1 ) + 1, TODAY () )
)
MTD New =
CALCULATE (
COUNT ( 'Cases'[Case Number] ),
DATESMTD ( 'Date2'[Date] )
)Small correction in the comments -
EOMONTH(TODAY(), -2) + 1 does not mean “go back 2 days + 1 day”. It means go to the end of the month — two months before today, then add 1 day — which gives the first day of the previous month.Check below comments added for full code for your reference -
New Cases this Month = VAR StartDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -2 ) + 1, -- If today is the 1st, use the first day of the previous month EOMONTH ( TODAY (), -1 ) + 1 -- Otherwise, use the first day of the current month ) VAR EndDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -1 ), -- If today is the 1st, use the last day of the previous month TODAY () -- Otherwise, use today ) RETURN CALCULATE ( COUNT ( 'Cases'[Case Number] ), DATESBETWEEN ( Date2[Date], StartDate, EndDate ) -- Count cases where Date2[Date] falls between StartDate and EndDate )💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
6 Replies
Hi ArchStanton,
Yes — this is happening because on 1st July, measures using TODAY() or DATESMTD() now evaluate against July, not June. If there are no July cases yet, the Card shows blank / --.
If you want the measure to show the current month during the month, but switch to the previous completed month on the 1st day, use below measure -
New Cases Month Display = VAR StartDate = IF( DAY(TODAY()) = 1, EOMONTH(TODAY(), -2) + 1, EOMONTH(TODAY(), -1) + 1 ) VAR EndDate = IF( DAY(TODAY()) = 1, EOMONTH(TODAY(), -1), TODAY() ) RETURN CALCULATE( COUNT('Cases'[Case Number]), DATESBETWEEN( Date2[Date], StartDate, EndDate ) )💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerThis works perfectly, thank you so much!
Just so I 100% understand it and, learn from it, do my comments accurately explain the code?New Cases this Month = VAR StartDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -2 ) + 1, // If today is the 1st of the month then go back 2 days +1 day so 30th Jun instead of 1st Jul EOMONTH ( TODAY (), -1 ) + 1 // otherwise use todays value ) VAR EndDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -1 ), TODAY () ) // If its the 1st of the month then go back 1 day otherwise don't RETURN CALCULATE ( COUNT ( 'Cases'[Case Number] ), DATESBETWEEN ( Date2[Date], StartDate, EndDate ) // count the cases opened between the 2 variables )Small correction in the comments -
EOMONTH(TODAY(), -2) + 1 does not mean “go back 2 days + 1 day”. It means go to the end of the month — two months before today, then add 1 day — which gives the first day of the previous month.Check below comments added for full code for your reference -
New Cases this Month = VAR StartDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -2 ) + 1, -- If today is the 1st, use the first day of the previous month EOMONTH ( TODAY (), -1 ) + 1 -- Otherwise, use the first day of the current month ) VAR EndDate = IF ( DAY ( TODAY () ) = 1, EOMONTH ( TODAY (), -1 ), -- If today is the 1st, use the last day of the previous month TODAY () -- Otherwise, use today ) RETURN CALCULATE ( COUNT ( 'Cases'[Case Number] ), DATESBETWEEN ( Date2[Date], StartDate, EndDate ) -- Count cases where Date2[Date] falls between StartDate and EndDate )💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
Alternative approach -
If you want to continue showing June data until 8:00 AM on 1st July, and then switch to July MTD from 8:00 AM onwards, you can use below measure -
New Cases Month Display Test = VAR UsePreviousMonth = DAY(NOW()) = 1 && HOUR(NOW()) < 8 VAR StartDate = IF( UsePreviousMonth, EOMONTH(TODAY(), -2) + 1, EOMONTH(TODAY(), -1) + 1 ) VAR EndDate = IF( UsePreviousMonth, EOMONTH(TODAY(), -1), TODAY() ) RETURN CALCULATE( COUNT('Cases'[Case Number]), DATESBETWEEN( Date2[Date], StartDate, EndDate ) )Short explanation:
- UsePreviousMonth checks if it's the 1st day of the month before 8 AM.
- If TRUE, the measure shows data for the entire previous month.
- If FALSE, it shows data for the current month.
- StartDate and EndDate define the date range to use.
- CALCULATE then counts the cases created between those dates.
Example on 1st July:
- 12:00 AM – 7:59 AM → Shows 1-Jun to 30-Jun (e.g., 597 cases).
- 8:00 AM onwards → Shows 1-Jul to Today (July MTD).
This prevents the card from going blank immediately after midnight on the 1st of the month.
Note - Power BI Service typically evaluates in UTC
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
Hi ArchStanton
Power BI Service uses UTC so TODAY(), NOW() and any other volatile function uses UTC and not the intended users timezone. Instead of using TODAY() use UTCNOW() then add/subtract the UTC offset as a fraction of a day.
Today Local TZ = VAR OffsetHours = 5.5 VAR LocalDateTime = UTCNOW () + DIVIDE ( OffsetHours, 24 ) RETURN DATEVALUE ( LocalDateTime )
