How to get Running Total for any aggregation

Hi Kalaivani 

Yes, it will work as you described! Here's how we can handle a dynamic running total in Power BI that adjusts automatically based on whether you're viewing the table by Year-Month or WeekBeginDate.

Your requirements are:

1. When collapsed to Year-Month, the running total should group by Year-Month.
2. When expanded to WeekBeginDate, the running total should group by WeekBeginDate.

We can achieve this with a DAX measure that checks the current hierarchy level using the ISINSCOPE function.

RunningTotalMissedTarget =
VAR FirstMissDate =
    CALCULATE(
        MIN('CalendarTable'[Date]),
        FILTER(
            ALL('Template Expectations'),
            [TemplateExpectations] < 0.9
        )
    )
RETURN
    IF(
        MAX('CalendarTable'[Date]) >= FirstMissDate,
        IF(
            ISINSCOPE('CalendarTable'[WeekBeginDate]),
            // Running total grouped by WeekBeginDate
            CALCULATE(
                COUNTROWS('Template Expectations'),
                FILTER(
                    'Template Expectations',
                    [TemplateExpectations] < 0.9 &&
                    'CalendarTable'[WeekBeginDate] <= MAX('CalendarTable'[WeekBeginDate])
                )
            ),
            // Running total grouped by Year-Month
            CALCULATE(
                COUNTROWS('Template Expectations'),
                FILTER(
                    'Template Expectations',
                    [TemplateExpectations] < 0.9 &&
                    'CalendarTable'[YearMonth] <= MAX('CalendarTable'[YearMonth])
                )
            )
        ),
        BLANK()
    )

The ISINSCOPE function checks if the current view in the table visual is at the WeekBeginDate level. If it is, the measure calculates the running total grouped by WeekBeginDate. If the view is at a higher level, such as Year-Month, the measure defaults to calculating the running total by Year-Month.

The measure starts counting from the first missed target date, using the FirstMissDate variable to ensure the running total begins only when a missed target is detected. This way, the measure adjusts dynamically based on the level of grouping in the table visual.

For example, when the table is grouped by Year-Month, the running total shows cumulative values for each month. If the table is expanded to display WeekBeginDate, the running total shows cumulative values for each week within the month. In this case, for January 2024, the running total for the month would be 5, while the weekly breakdown would be 1 for January 1, 2 for January 8, and 3 for January 15. For February 2024, the running total for the month would be 7, with 4 for February 5.

Yes, this will work for your case. The measure dynamically adjusts based on whether the table is grouped by Year-Month or expanded to WeekBeginDate in a hierarchical table visualization. There is no need for manual adjustments, as the measure handles both grouping levels seamlessly.

Best regards,