Forum Discussion

ThisIsBlecki's avatar
ThisIsBlecki
Frequent Visitor
1 year ago

monthly Case Count Incorrect for Rolling/YTD Totals

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,

 

Need Help
Tips and Tricks
Tutorial Requests

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.