Creating a conditional progressive average based on a date value

[BJE]

I have created a date based table which looks like this:

Year	Month Start Date	Month End Date	Year YTD Start Date	Year YTD End Date	FTE
2018-19	1/07/2018	31/07/2018	1/07/2018	31/07/2018	473.6
2018-19	1/08/2018	31/08/2018	1/07/2018	31/08/2018	474.6
2018-19	1/09/2018	30/09/2018	1/07/2018	30/09/2018	478.3
2018-19	1/10/2018	31/10/2018	1/07/2018	31/10/2018	487.1
2018-19	1/11/2018	30/11/2018	1/07/2018	30/11/2018	492.1
2018-19	1/12/2018	31/12/2018	1/07/2018	31/12/2018	493.1
2018-19	1/01/2019	31/01/2019	1/07/2018	31/01/2019	497.7
2018-19	1/02/2019	28/02/2019	1/07/2018	28/02/2019	508
2018-19	1/03/2019	31/03/2019	1/07/2018	31/03/2019	512.4
2018-19	1/04/2019	30/04/2019	1/07/2018	30/04/2019	514.4
2018-19	1/05/2019	31/05/2019	1/07/2018	31/05/2019	518.4
2018-19	1/06/2019	30/06/2019	1/07/2018	30/06/2019	521.4
2019-20	1/07/2019	31/07/2019	1/07/2019	31/07/2019	527.4
2019-20	1/08/2019	31/08/2019	1/07/2019	31/08/2019	531.8
2019-20	1/09/2019	30/09/2019	1/07/2019	30/09/2019	535.8
2019-20	1/10/2019	31/10/2019	1/07/2019	31/10/2019	541.2
2019-20	1/11/2019	30/11/2019	1/07/2019	30/11/2019	548.2
2019-20	1/12/2019	31/12/2019	1/07/2019	31/12/2019	551.9
2019-20	1/01/2020	31/01/2020	1/07/2019	31/01/2020	557.9

I want to be able to calculate a conditional progressive average for all values in a given year (i.e. YTD average). For instance for the year 2018-19, for July 2018 the result should be 473.6, for August 2018 the result should be 474.1 (being the average of July and August), for September 2018 thes result should be 475.5 (being the average of July, August and September) and so on. When it gets to 2019-20, the average calculations should start again.

Advice on working through this as an added column greatly appreciated.

[ziying35]

Hi,@BJE

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rdW9bsIwFAXgV4k8g/FvfM0M3TqVBZUOkYrUCSSUreq7FxKX2FZjc7HnYx19OST4/Zvsj92FrIlgHJbckgV5PZ/6r+at7y59s+n64zXkhxUzh9XtzP3A9vT5F8swvzU2+90m1XE/M1/zstuStTKStj+Lx5mQYUIdJsRMhWPaBJMF+dPMsMYxgUoEk7P0ml5esqZXMzLBUI5h8vSaXl6yplczMq3AMUVmTVFnTREzJYrJxse0s5/QlBd9QlONYxpqMEwxzxQQ5M8yo5qBqRlgkDKzpayzpYyQXFCFYaoEkwV50d+RipkKx9SZNXWdNXXMBByzzazZ1lmzjZiCx0y7FCx3o8+vaR5m2tSaJmYaHBMyTKjDhIgpOQUM02Z+dFvODGscU6OY7qqdXdPLS9b0akam4lRgmKk7iAV5yZpejWMCjpm4g2SYF60Z30GaU4t5N4fH9A78exUPedEnNNU4prkyP34B",BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source),
    acc = List.Accumulate(
              rows,
              {"", "", 0, {}},  //Year/counter/running total/output
              (s,c)=>
                  if c{0}<>s{0} then {c{0}, 2, c{5}, s{3}&{c&{c{5}}}} 
                  else {c{0}, s{1}+1, c{5}+s{2}, s{3}&{c&{(c{5}+s{2})/s{1}}}}
           ){3},
    tbl = Table.FromRows(acc, Table.ColumnNames(Source)&{"Avg"})
in
    tbl

If you don't have a lot of data, try my code above.

If my code solves your problem, please mark it as a solution

[BJE · Accepted solution]

Thanks - have resolved my issue using grouping of data and then using both the index and year columns to calculate a filtered sum function, divided by index number which works.

[v-eachen-msft]

Hi @BJE ,

Have you resolved the problem? You could accept any reply to close this topic.

[lbendlin]

In DAX you should be able to use AVERAGEX() with ALLEXCEPT(year). Or did you want this in M ?

[BJE]

Yes, I need this in M.