topic Re: Average for each 4 Quarter - Moving Average DAX in DAX Commands and Tips

Average for each 4 Quarter - Moving Average DAX

Anonymous — Wed, 01 May 2024 11:22:30 GMT

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)

Re: Average for each 4 Quarter - Moving Average DAX

lbendlin — Wed, 01 May 2024 20:14:48 GMT

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-Forum/ba-p/963216

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/1447523

Re: Average for each 4 Quarter - Moving Average DAX

Anonymous — Wed, 15 May 2024 05:19:39 GMT

Hi Anonymous ,
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