CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Jumans
Jumans

Reputation: 29

Multiplying a current day's value by the next day's value

I have a date_column, an X_column and a sales_column.

01/01/2022 | 3 | 50
02/01/2022 | 4 | 10
03/01/2022 | 1  | 5

and I want to multiply:

50 * 4 = 200
10*1 = 10
...

Upvotes: 0

Views: 234

Answers (1)

horseyride
horseyride

Reputation: 21298

Powerquery ...

If the dates are always consecutive, already sorted by date then most understandable way is :

  • Add column, index column
  • Add column, custom column with formula 
    = #"Added Index"{[Index]+1}[Column1]
  • Click select the three numerical columns, transform, data type decimal
  • Add column, custom column with formula 
    =[Column2]*[Custom]

    That will multiply them on each row. The bottom item will return an error that you could replace with something (right-click column, replace error, and put something else in there in instead)

    sample code:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]+1}[Column1]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column2", type number}, {"Index", type number}, {"Custom", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Column2]*[Custom])
in  #"Added Custom1"

    A more advanced way will do the calculation regardless of the sort order of the data, and will return an error if there is no match for the next day (you could right-click replace error, and put something else in there in instead). Assumes columns in question are called date, Column1 and Column2

    add column ... custom column ... with code

    (i)=>Table.SelectRows(Source, each [date]=Date.AddDays(i[date],1))[Column1]{0}

    then follow steps above for [Column2]*[Custom]

    sample full code:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source,"Offset",(i)=>Table.SelectRows(Source, each [date]=Date.AddDays(i[date],1))[Column1]{0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Column2]*[Offset])
in  #"Added Custom1"

    Upvotes: 1

    • Related Questions