Grap balance/value from previous year via Power Query

[carlwibe]

Hi,

A bit desperate so I post a question here as well... Looking for a way to retrieve the closing balance from the previous year but fail due to poor knowledge.

For all months in the current year, I want to put the previous year's closing balance in a new column, for example named "Latest/December Value Last Year". Attaching a picture where columns A, B and C are my "report data" and column E is the output I would like to have in my Power Query.

Is there a "simple" and understandable way to solve this in Power Query and M?

Thanks in advance!

[carlwibe]

Thanks @slorin... that's absolutely a good solution based on my question.

However, due to the fact that my data base is "large" (approximately 60 months times 7,000 rows), this solution seems to significantly slow down data loading. I accept your solution as "accepted" but will think further... mabye is the best soluton to find a way to do this with a measure instead.

[slorin · Accepted solution]

Hi, @carlwibe 

let
Source = YourSource,
#"December Last Year" = Table.AddColumn(YourSource, "December Last Year", each #date(Date.Year([Date])-1, 12, 1), type date),
AutoJoin = Table.NestedJoin(#"December Last Year", {"December Last Year"}, #"December Last Year", {"Date"}, "December Last Year.1", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(AutoJoin, "December Last Year.1", {"Balance"}, {"Latest/December Value Last Year"})
in
Expand

Stéphane