Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
1 month ago
Solved

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 Lev...
Need Help
  • Rupa01's avatar
    Rupa01
    1 month ago

    ArchStanton 

    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  
Rupa01's avatar
Rupa01
Solution Sage
1 month ago

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 Engineer 
  • ArchStanton's avatar
    ArchStanton
    Power Participant
    1 month ago

    This 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
    )
    • Rupa01's avatar
      Rupa01
      Solution Sage
      1 month ago

      ArchStanton 

      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  
      • ArchStanton's avatar
        ArchStanton
        Power Participant
        1 month ago

        Thanks for your help, this works perfectly!

  • Rupa01's avatar
    Rupa01
    Solution Sage
    1 month ago

    ArchStanton 

    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