Moving Average Calculation Difference

[yogeshk77]

I need a help to compare what's causing the difference of output between two identical formulas & how to fix it please.

Refer to this OldPBI, whcih is showing a moving average of "last 6 sprints" (Last column) of story points.

This DAX measure is not showing the result as expected.

I then copied that single table data to another PBI called NewPBI, and there it's working well.

Can someone please do a quick review of the measures & relationships & suggest me what's causing this difference & How do fix teh OldPBI to show correct moving averages?

[yogeshk77]

You are a genius.. Thank you so much !!!

[v-yiruan-msft · Accepted solution]

Hi @yogeshk77 ，

As checked the two pbix files you provided, and in the new file, you get the summary value for the field [Story_Points_10004] directly. In the old file you can also modify the measure [Last 6 Avg] as follows with the similar idea: first get the summary value of each directory, and then get the desired result based on the conditions.

Measure = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( WeightedVel ),
        WeightedVel[Learnosity_Team_10202],
        WeightedVel[Reporting Sprint ID],
        WeightedVel[Reporting Sprint Name],
        "SUMOFSP", CALCULATE ( SUM ( WeightedVel[Story_Points_10004] ) )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "RANK",
            RANKX (
                FILTER (
                    _SUMMARIZE,
                    [SUMOFSP] > 0
                        && [Learnosity_Team_10202] = EARLIER ( [Learnosity_Team_10202] )
                ),
                [Reporting Sprint ID],
                ,
                DESC,
                DENSE
            )
    )
VAR _ADDLAST6AVG =
    ADDCOLUMNS (
        _ADDRANK,
        "LAST6AVG",
            IF (
                [RANK] <= 6,
                AVERAGEX (
                    FILTER (
                        _ADDRANK,
                        [Learnosity_Team_10202] = EARLIER ( [Learnosity_Team_10202] )
                            && [RANK] >= EARLIER ( [RANK] )
                            && [RANK]
                                <= EARLIER ( [RANK] ) + 5
                    ),
                    [SUMOFSP]
                )
            )
    )
RETURN
    SUMX (
        FILTER (
            _ADDLAST6AVG,
            [Learnosity_Team_10202] = MAX ( WeightedVel[Learnosity_Team_10202] )
                && [Reporting Sprint ID] = MAX ( WeightedVel[Reporting Sprint ID] )
        ),
        [LAST6AVG]
    )

Best Regards