Search code examples
powerbipowerquerym

Calculated columns bases on the past 3 months in Power query

First time trying to use M in power query... what I have is this table

table

I need to create two columns that per each row (combination of CD_Loja x CD_Produto )returns me the sum of QT_VENDA for that combination divided by the # of days in the past 3 months. The other column is pretty much the same but with the sum of  VL_VENDA_LIQ  Instead.

I.e: For the first row I want to sum up all QT_VENDA that matches CD_PRODUTO =1001930 AND CD_LOJA = 151 in the past 3 months (the DATE column has daily data) and divide it by the number of days in those 3 months.

Is there a way to do so ? And how do I go about this ?

Thanks in advance.

Solution

  • In powerquery, M, something along these lines

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"DATA", type date}, {"CD_PRODUCTO", type text}, {"CD_LOIA", type text}, {"QT_VENDA", Int64.Type}, {"VL_VENDA_LIQ", Int64.Type}})),
#"Added Custom" = Table.AddColumn(#"Changed Type" ,"QT_VENDA_90",(i)=>List.Average(Table.SelectRows(#"Changed Type" , each [CD_PRODUCTO]=i[CD_PRODUCTO] and [CD_LOIA]=i[CD_LOIA] and [DATA] <= i[DATA] and [DATA] >= Date.AddDays(i[DATA] ,-90)) [QT_VENDA]), type number),
#"Added Custom2" = Table.AddColumn(#"Added Custom" ,"VL_VENDA_LIQ_90",(i)=>List.Average(Table.SelectRows(#"Changed Type" , each [CD_PRODUCTO]=i[CD_PRODUCTO] and [CD_LOIA]=i[CD_LOIA] and [DATA] <= i[DATA] and [DATA] >= Date.AddDays(i[DATA] ,-90)) [VL_VENDA_LIQ]), type number)
in #"Added Custom2"