Average for each 4 Quarter - Moving Average DAX

[RajK2]

Dear Team,

I would like to calculate moving average as my DAX Formuls didn't works well.

My fact table doesn't have value on specific date/month  then my 4 Quarter average doesn't calculate as expected.

Example measurement takes where we don't have value in Fact Table then it takes SUM of Total / 4 as average (wrong answer) = 34,637

How can we fix this. pls suggest

VAR __LAST_DATE = ENDOFQUARTER('Calendar'[EO_Month])

VAR __PERIOD = DATESBETWEEN(

            'Calendar'[EO_Month],

            STARTOFQUARTER(DATEADD(__LAST_DATE, -3, QUARTER)),

            ENDOFQUARTER(DATEADD(__LAST_DATE, 0, QUARTER))

        )

RETURN

    CALCULATE(AVERAGEX(

    VALUES('Fact'[Date]),

    IF([Values]=BLANK(),0,[Values])

),__PERIOD)

[Anonymous · Accepted solution]

Hi @RajK2 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create two columns

YearQtr = CONCATENATE(LEFT([Year-Qtr],2),RIGHT([Year-Qtr],1))

Rank = RANKX('Table',[YearQtr],,ASC)

Create measure

Average 4 quarter = 
VAR _sum = 
CALCULATE(
    SUM('Table'[AOP$]),
    FILTER(
        ALL('Table'),
        'Table'[Rank] >= SELECTEDVALUE('Table'[Rank]) - 3 && 'Table'[Rank] <= SELECTEDVALUE('Table'[Rank])
    )
)
RETURN
IF(
    _sum = BLANK(),
    0,
    _sum/4
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...