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!