Wrong Value for Rolling/KPI When Adding Time to Visual

[ThisIsBlecki]

Hi everyone,

I’m facing an issue with a KPI in Power BI that uses a Calculation Group to calculate both a rolling 12-month value and a cumulative YTD value. The KPI visual in Power BI requires a trend axis (typically the date), so I can’t display the KPI without a time dimension.

The problem:

As soon as I add the time dimension (e.g., the month) as the trend axis to the KPI visual, the calculation for the rolling 12 months (or YTD) returns the wrong value per month. However, when I show the value in a table without breaking it down by time, the total is correct. But as soon as I include the time dimension, the individual monthly values are wrong (and so is the last row, which should reflect the current period).

Measure:

My_KPI = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( FactTable[ID] ),
            "TotalValue",
            CALCULATE(
                SUM( FactTable[Value] ),
                DimIndicator[IndicatorCode] = "SOME_INDICATOR",
                FactTable[Value] <> 0,
                IF(
                  ISCROSSFILTERED( DimOrgUnit ),
                  TRUE(),
                  DimOrgUnit[OrgCode] <> "SomeOrg"
                )
            )
        ),
        [TotalValue] <> 0
    )
)

CGe Rolling

Rolling =
CALCULATE(
    -- Calculate the currently selected measure over the desired time window
    SELECTEDMEASURE(),

    -- Create a date filter for the last 12 months
    DATESINPERIOD(
        DimDate[Date],         -- Date column from calendar table
        MAX(DimDate[Date]),    -- End point: latest date in current filter context
        -12,                   -- Window size: 12 periods back
        MONTH                  -- Period: months
    )
)

CGE Cumulative

Cumulative =
TOTALYTD(
    SELECTEDMEASURE(),        // Takes the currently selected measure in the visual
    DimDate[Date]             // Date column from the calendar table
)

What I want:

• I want to display a KPI card showing the correct rolling 12M or LTD value (depending on the selected calculation group)

• At the same time, I want to display a time series that outputs the correct calculated value for each month.

  In my case, the time series can cover up to 5 years, so each month within this range should display its correct value according to the selected calculation (rolling 12 months, cumulative YTD, etc.).

Screenshot:

In my screenshot, the overall total is correct, but when the time axis is included (as required for the KPI visual), the numbers are off for each month/row.

Questions:

• Has anyone run into this issue with Calculation Groups and time-based visuals in Power BI?

• Are there any best practices or DAX patterns to ensure the KPI visual shows the correct value for the current period, while also allowing a correct time series?

Thanks for any tips or pointers!

[ThisIsBlecki]

While all sum measures (e.g. the pure Cost value or rolling sums) deliver exactly the expected figures both in the overall indicator and per period (month), there are only deviations in the monthly break-down in the number of cases (Distinct Count of the accident_NR).

- Sum vs. period value: Regardless of whether you output the ‘Total’ or a single month, the sum of all TG values per measure matches in both views.

- Case count: The same Distinct Count approach provides exactly the correct value in the KPI Card Total, but shows partially incorrect case counts in the matrix per month.

Important: The totals calculations are correct both for the total and for each individual period (month) - the problem only occurs with the count logic.

Does anyone have an explanation or a robust pattern on how to implement a rolling distinct count of cases in Power BI so that it is consistently correct in the monthly breakdown and in the total indicator?

One additional consideration: it looks like some cases post positive TG adjustments in one month and negative adjustments in another, effectively canceling each other out over the rolling window. That means a case may be “active”  in one monthly snapshot but “inactive” in another, which can lead to unexpected fluctuations in the per‐month distinct‐count even though the overall total remains correct.

And if a case’s positive and negative TG adjustments exactly cancel each other out over the rolling window (net sum = 0), that case should be completely excluded from the distinct‐count—it should not be counted as active in any monthly snapshot.

[v-sshirivolu]

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

Create this using DAX:

DimDate = 
ADDCOLUMNS(
    CALENDAR(DATE(2021,1,1), DATE(2024,6,30)),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

Create the relationship.

Drag FactTable[Date] onto DimDate[Date] to create the relationship.

Right-click DimDate → Mark as Date Table → select Date.

Add DAX Measures

Add New Measure to FactTable  

Monthly KPI  :

Monthly KPI :=
CALCULATE(
    SUM(FactTable[Value]),
    FactTable[IndicatorCode] = "SOME_INDICATOR",
    FactTable[OrgCode] = "SomeOrg"
)

YTD KPI :

YTD KPI :=
TOTALYTD(
    [Monthly KPI],
    DimDate[Date]
)

Rolling 12M KPI :

Rolling 12M KPI :=
CALCULATE(
    [Monthly KPI],
    DATESINPERIOD(
        DimDate[Date],
        LASTDATE(DimDate[Date]),
        -12,
        MONTH
    )
)

Rolling Distinct Count of Cases : 

Rolling Distinct Cases :=
CALCULATE(
DISTINCTCOUNT(FactTable[CaseID]),
DATESINPERIOD(
DimDate[Date],
LASTDATE(DimDate[Date]),
-12,
MONTH
)
)

Create Visuals:

A. Card Visual

Add Rolling 12M KPI.

It shows the current 12-month rolling value.

B. Matrix Visual (to show per month)

Rows: DimDate[YearMonth]

Values: Rolling 12M KPI, Monthly KPI, YTD KPI.

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,

Sreeteja.
Community Support Team 

[v-sshirivolu]

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.

[v-sshirivolu]

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.

[v-sshirivolu]

Hi @ThisIsBlecki ,

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

Thank you

[ThisIsBlecki · Accepted solution]

Hi @v-sshirivolu , 

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-sshirivolu]

Hi @ThisIsBlecki ,

Thanks so much for the update and no worries at all about the delay!

ISince the entire calculation logic has been moved to the Data Warehouse and the issue appears to be resolved, I’ll go ahead and close this thread. With the changes in place and no further action required, this thread will be closed.

regards,
Sreeteja