Increase from the previous month

[lionelv]

Hi,

I try to increase the column Amount from the previous month with an Increase factor. In Excel, it’s super easy to do. In D8 you write: “=D7*C8” and you drag down the formula. But how to do that with PowerQuery?

I see two sub-problems (but maybe I’m just taking the problem on the wrong side):

• How to merge Historical data and New Measure?
• How to use previous month as input to increase current month? And so on?

Source	Month	Increase	Amount
Histo	2015-01-01	1.009	$        327.46
Histo	2015-01-02	1.003	$        328.44
Histo	2015-01-03	1.002	$        329.10
Histo	2015-01-04	1.005	$        330.75
Histo	2015-01-05	1.012	$        334.72
Histo	2015-01-06	1.007	$        337.06
New measure	2015-01-07	1.001	???
New measure	2015-01-08	1.002	???
New measure	2015-01-09	1.005
New measure	2015-01-10	1.013
New measure	2015-01-11	1.014
New measure	2015-01-12	1.011

Thank for your help

[adudani]

hi @lionelv,

Try this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZIxCsMwDEWvYjynQrLkOL5Bp14gZOjgoUMpNC29Ts/SkzUlGlyIHGwwhvck9O1x9MfL/Lj5zgekeEBa9nIhQMzL6T7v/+U4JJDe+anbUIOqbKgDiBgqqxoMNQOhoYqqcVtlhBQNNa4qGV1ZIAVD7bVrMtQEqDGdystdy3l+3ktdIGmBX94tbqiSaXG5iqHBEerMvMPpPyDZ4fTRaZ1j+gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, Month = _t, Increase = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Month", type date}, {"Increase", type number}, {"Amount", type number}}),
#"Added Index_0..n" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1_1...n" = Table.AddIndexColumn(#"Added Index_0..n", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1_1...n", {"Index"}, #"Added Index1_1...n", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded table to get previous month amount" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Amount"}, {"Added Index1.Amount"}),
#"Final Amount( prev month $ * increase)" = Table.AddColumn(#"Expanded table to get previous month amount", "Final Amount( prev month $ * increase)", each [Added Index1.Amount]*[Increase]),
#"Changed Type1" = Table.TransformColumnTypes(#"Final Amount( prev month $ * increase)",{{"Final Amount( prev month $ * increase)", type number}})
in
#"Changed Type1"Appreciate a thumbs up if this was helpful.

_____________

steps taken.

1. create an index column from 0 (col1).

2. create an index column from 1 (col2).

3. left join/ self merge table on index column (col1 on col2).

4. expand the table for previous month's amount.

5. create custom column to calculate previous month's amount * increase for current month.

Please accept this as a solution if the question is solved.

[lionelv]

Hi adudani,

Thanks, but it add only one new value and in an other column. Consequantly, it stop there and does'nt continu untile the end of the list. As far as I read sefl referencing column are not allowed in PowerQuery. I still try to find a solution. 

[adudani]

@lionelv , thanks for letting me know.

Maybe this is helpful: Get Value from Previous Row using Power Query - BI Gorilla