topic Re: Cummulative Average in DAX Commands and Tips

Cummulative Average

gerardotrejogcg — Fri, 09 Apr 2021 04:36:53 GMT

I have a cost table per week of year (page filtered by this year). I want to set the cummulative average per week.

LT 2 - 95.4

LT 3 - 89 ( [95.4+82.6] / 2)

LT 4 - 88.8 ( [95.4+82.6+88.4] / 3)

wanted to use an average but no way how to start with this.

Re: Cummulative Average

amitchandak — Fri, 09 Apr 2021 05:06:50 GMT

gerardotrejogcg , Try a new column like

averageX(filter(allselected(Table), Table[segement]= max(Table[Segment]) && Table[Week] <= max(Table[Week])),Table[Value])

in place of this column Table[Value], you can use a measure too

I have assume week on column of matrix

Re: Cummulative Average

gerardotrejogcg — Mon, 12 Apr 2021 16:48:32 GMT

amitchandak Thanks for your reply. Actually I have another complexity. My Date table is populated from Actuals Date (Start) and Forecast Date (End) ... Eg. Jan 1st, 2015 - Dec 31st, 2021.

Since my measure is using Actuals the last date is April 2021 so no issues.

SCM Price P/U = DIVIDE ( [SCM Sales] , [SCM Units])

But I want to use your code it's populating the 53 weeks of year. How can I filter by the last "Actual" week of year ?

SCM Price P/U Avg =

AVERAGEX ( FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Week of Year] <= MAX ( 'Date'[Week of Year] ) ),

[SCM Price P/U] )