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

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!