Forum Discussion

ThisIsBlecki's avatar
ThisIsBlecki
Frequent Visitor
1 year ago
Solved

Wrong Value for Rolling/KPI When Adding Time to Visual

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 ...
Need Help
Tutorial Requests
  • ThisIsBlecki's avatar
    ThisIsBlecki
    1 year ago

    Hi Anonymous , 

     

    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.

     

ThisIsBlecki's avatar
ThisIsBlecki
Frequent Visitor
1 year ago
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.

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 

     

    • 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.

       

      • 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.