Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RajK2
Helper IV
Helper IV

Average for each 4 Quarter - Moving Average DAX

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)

 

Capture1.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

vheqmsft_0-1715750205933.png

Create two columns

YearQtr = CONCATENATE(LEFT([Year-Qtr],2),RIGHT([Year-Qtr],1))
Rank = RANKX('Table',[YearQtr],,ASC)

vheqmsft_1-1715750261371.png

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

vheqmsft_2-1715750298289.png

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

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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:

vheqmsft_0-1715750205933.png

Create two columns

YearQtr = CONCATENATE(LEFT([Year-Qtr],2),RIGHT([Year-Qtr],1))
Rank = RANKX('Table',[YearQtr],,ASC)

vheqmsft_1-1715750261371.png

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

vheqmsft_2-1715750298289.png

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
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.