Insert rows with future months with mean calculus

[powerbricco]

Dear All,

I have the following table in PowerQuery:

DATE	VALUE
28/11/2024	3765
29/11/2024	2467
10/12/2024	1447
03/01/2025	1097
06/01/2025	2882
23/01/2025	3732
07/02/2025	4448
15/02/2025	3082

What I need to achieve is to create one row for the future 24 months beginning from the last month on table +1 and every row value should have the mean of the previous months of last year (years before last year should not be included in the mean).

My desired result is as follows:

DATE	VALUE
28/11/2024	3765
29/11/2024	2467
10/12/2024	1447
03/01/2025	1097	values included in the mean calculus
06/01/2025	2882
23/01/2025	3732
07/02/2025	4448
15/02/2025	3082
31/03/2025	3048
30/04/2025	3048
31/05/2025	3048
30/06/2025	3048
31/07/2025	3048
31/08/2025	3048
30/09/2025	3048
31/10/2025	3048
30/11/2025	3048
31/12/2025	3048
31/01/2026	3048
28/02/2026	3048
31/03/2026	3048
30/04/2026	3048
31/05/2026	3048
30/06/2026	3048
31/07/2026	3048
31/08/2026	3048
30/09/2026	3048
31/10/2026	3048
30/11/2026	3048
31/12/2026	3048
31/01/2027	3048
28/02/2027	3048

Do you think it is possible?
Thank you a lot

[ryan_mayu · Accepted solution]

@powerbricco 

pls see if this is what you want

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JDcAgDETRXnxG8go2taD030YCDpKvT18za4EEMqOQGDRQHx2e9uksKjb8KBOyXGWzVFKk0/atNH8dRSVCcre26ppKjiRXzSzyrRdV2gvPCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, VALUE = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"DATE", type date}}, "en-AU"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"VALUE", Int64.Type}}),
ThisYear = let
MaxYear = List.Max(List.Transform(#"Changed Type"[DATE], Date.Year)),
ThisYear = MaxYear
in
ThisYear,
ThisYearRows = let
ThisYearRows = Table.SelectRows(#"Changed Type", each Date.Year([DATE]) = ThisYear)
in
ThisYearRows,
MeanThisYear = let
MeanThisYear = List.Average(ThisYearRows[VALUE])
in
MeanThisYear,
FutureDates = let
LastDate = List.Max(#"Changed Type"[DATE]),
StartDate = Date.AddMonths(Date.EndOfMonth(LastDate), 1),
FutureDates = List.Transform({0..23}, each Date.EndOfMonth(Date.AddMonths(StartDate, _)))
in
FutureDates,
FutureRowsWithValue = let
FutureRows = Table.FromList(FutureDates, Splitter.SplitByNothing(), {"DATE"}),
FutureRowsWithValue = Table.AddColumn(FutureRows, "VALUE", each MeanThisYear)
in
FutureRowsWithValue,
Custom1 = let
AllRows = Table.Combine({#"Changed Type",FutureRowsWithValue})
in
AllRows
in
Custom1

[Ashish_Mathur]

Hi,

Would you be OK with a DAX measure solution?

[wardy912]

Hi @powerbricco 

 This can be achieved with the following steps in power query

Get last date of your table:

LastDate = List.Max(Source[DATE])

Get last year:

LastYear = Date.Year(LastDate)

Filter rows for last year only:

LastYearRows = Table.SelectRows(Source, each Date.Year([DATE]) = LastYear)

Calculate Mean:

MeanValue = List.Average(LastYearRows[VALUE])

Generate a list of future months:

StartMonth = Date.AddMonths(Date.StartOfMonth(LastDate), 1),
FutureMonths = List.Transform({1..24}, each Date.EndOfMonth(Date.AddMonths(StartMonth, _ - 1)))

Convert this list to a table:

FutureTable = Table.FromList(FutureMonths, Splitter.SplitByNothing(), {"DATE"}),
FutureTable = Table.AddColumn(FutureTable, "VALUE", each MeanValue)

Combine original table with future table:

FinalTable = Table.Combine({Source, FutureTable})

This should add 24 new rows with dates as the last day of each month and the calculated mean value

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!