topic Re: Urgent assistance: Calculate the moving average of a measure in DAX Commands and Tips

Urgent assistance: Calculate the moving average of a measure

JDU — Sat, 04 Feb 2023 15:36:19 GMT

Dear friends, I would like to ask for your kind assistance in solving the following problem:

The last column of the following image is the monthly average of each month calculated from the DAX Average measure (column 3/column 4).

And what I need to do is to get the quarterly and semi-annual moving average of that measure (last column).

Example: (366+349+328)/3 = 347.6 ; (349+328+362)/3 = 343.3 and so on (idem with semester)

and (366+349+328+362+339+345)/6

I tried some solutions with SUMX and DATEINSPIRIOD but it always gives me a wrong number.

Thanks for your assistance.

Re: Urgent assistance: Calculate the moving average of a measure

Greg_Deckler — Sat, 04 Feb 2023 15:43:42 GMT

JDU See if this helps: Better Rolling Average - Microsoft Power BI Community

Re: Urgent assistance: Calculate the moving average of a measure

JDU — Sat, 04 Feb 2023 18:52:27 GMT

Thank you very much Greg for your collaboration, I did not know that solution, very useful, however, the result is not the expected one, it is very close but not correct.

Re: Urgent assistance: Calculate the moving average of a measure

FreemanZ — Sun, 05 Feb 2023 03:18:18 GMT

hi JDU

seems not difficult. could you paste your sample dataset, ideally in EN?

Re: Urgent assistance: Calculate the moving average of a measure

Anonymous — Mon, 06 Feb 2023 02:54:17 GMT

Hi JDU

You can refer to the following example.

The sample data table

And The Average has been created in the table

Create two measures in table

quarterly = var _filter=FILTER(ALLSELECTED('Table'),EOMONTH('Table'[Date],0)<=EOMONTH(MAX('Table'[Date]),0)) var _top3=TOPN(3,_filter,[Date],DESC) return DIVIDE(SUMX(_top3,ROUND([Average],0)),3) semi-annual = var _filter=FILTER(ALLSELECTED('Table'),EOMONTH('Table'[Date],0)<=EOMONTH(MAX('Table'[Date]),0)) var _top6=TOPN(6,_filter,[Date],DESC) return DIVIDE(SUMX(_top6,ROUND([Average],0)),6)

Output

Best Regards!

Yolo Zhu

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