Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
Hi @BJE ,
Have you resolved the problem? You could accept any reply to close this topic.
In DAX you should be able to use AVERAGEX() with ALLEXCEPT(year). Or did you want this in M ?
Yes, I need this in M.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |