Urgent assistance: Calculate the moving average of a measure

[JDU]

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.

[Anonymous · Accepted solution]

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.

[FreemanZ]

hi @JDU 

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

[Greg_Deckler]

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

[JDU]

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.