monthly Case Count Incorrect for Rolling/YTD Totals

[ThisIsBlecki]

Title: Rolling 12-Month or YTD Case Count Is Correct Overall, But Monthly Breakdown Shows Wrong Values

Hello everyone,

I’m facing the following issue in Power BI/DAX and would appreciate your help:

1. Scenario

• I have a table of cases (e.g., Table_Cases[CaseID]) with a numeric measure column (e.g., Table_Cases[Value]).
• Using a calculation group, I calculate:
• a rolling 12-month total, and
• a year-to-date (YTD) cumulative total.
• In the overall result (when not slicing by month), the numbers are correct.

1. Problem

When I add Month to a matrix or table:

• the overall total remains correct,
• but the value for the specific month is wrong (either too low or zero).

I want to count only those cases whose rolling or YTD total is greater than zero.

1. Anonymized DAX Code

CaseCount_Positive =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table_Cases[CaseID] ),
            "MonthlyOrYTDTotal",
            CALCULATE(
                SUM( Table_Cases[Value] ),
                Table_Metrics[MetricCode] = "KPI",
                Table_Cases[Value] <> 0,
                IF(
                    ISCROSSFILTERED( Table_Org ),
                    TRUE(),
                    Table_Org[OrgCode] <> "Org"
                )
            )
        ),
        [MonthlyOrYTDTotal] <> 0
    )
)

• VALUES(Table_Cases[CaseID]) lists each unique case.
• CALCULATE(...) sums the case’s value under the current calculation group (rolling or YTD),
• excludes zero values, and
• optionally filters out a specific organization (“Suva” in this example).
• The outer FILTER(... [MonthlyOrYTDTotal] <> 0) then ensures only cases with a positive total are counted.

1. Expected Behavior

• When slicing by Month (e.g., “March 2025”) → count all cases whose rolling-12-month sum in March 2025 is > 0.
• Overall (no month slice) → correct total number of cases for the selected period (YTD or rolling).

1. Actual Behavior

• Overall: ✔️ correct
• By Month: ✖️ wrong

It seems the combination of my DAX measure with the calculation group isn’t filtering properly at the single-month level.

1. Tried So Far

• Swapped VALUES for ALLSELECTED → no change
• Created a separate measure for the rolling sum and referenced it directly → same issue
• Removed the organization filter entirely → problem persists
• Tested ISCROSSFILTERED vs. ISFILTERED → no effect

1. Questions

1. Why is the case count correct overall but incorrect when sliced by month?
2. How can I modify my DAX so the filter respects the individual month context?
3. Are there any best practices for counting “cases with a positive rolling/YTD total” when using calculation groups?

The Sum of the KPI is having the correct value for total and on month.

Thank you for any insights, example DAX snippets, or explanations!

Best regards,

[v-hjannapu]

Hi @ThisIsBlecki,
Thank you for reaching out to the Microsoft fabric community forum.

The total count looks correct because it evaluates the entire dataset. But when you break it down by month, the DAX measure might not be using each row’s date correctly. As a result, it applies the same total across all months making them look identical.
Use something like MAX(DimDate[Date]) to grab the specific date for each row in your visual. Then plug that into DATESINPERIOD or TOTALYTD, so your measure recalculates based on the correct month.
Keep your base KPIs clean and reusable, and always use the current row’s date in your time filters. If using calculation groups, be extra careful  they can change filter context unexpectedly, so anchor your dates explicitly.

Rolling Distinct Cases (Filtered) :=
VAR CurrentMonth =
    MAX(DimDate[Date])  -- Gets the current row-level month in visual
RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES(FactTable[CaseID]),
            CALCULATE(
                SUM(FactTable[Value]),
                FactTable[IndicatorCode] = "SOME_INDICATOR",
                FactTable[OrgCode] = "SomeOrg",
                DATESINPERIOD(
                    DimDate[Date],
                    CurrentMonth,
                    -12,
                    MONTH
                )
            ) > 0
        )
    )
)

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team 

[v-hjannapu]

Hi @ThisIsBlecki ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @ThisIsBlecki ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @ThisIsBlecki,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank You.

Harshitha.

Community Support Team.

[ThisIsBlecki]

Hi @v-hjannapu , 

Unfortunately, it didn’t work. Power BI applies a strange time filter that I can’t make sense of. Unfortunately, I can’t find a solution without Tabular Editor and DAX Studio.

I’ve moved the entire calculation logic to the data warehouse.

Thanks for your help, and sorry for the late reply — I was away.

[v-hjannapu]

Hi @ThisIsBlecki ,
Thank you for confirming that the logic has been successfully moved to the data warehouse.

Regards,
Harshitha.