Calculated columns bases on the past 3 months in Power query

0
votes

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.

2
powerbipowerquerym
What is the definition of "the past 3 months"? The past 90 days? Based on name of the month I am comparing to -- (June pulls all prior days in June + May + April, or perhaps June pulls all prior days in May + April + March? – horseyride
Past 3 months would be something like today() - 90 days – Talles Lessa

2 Answers

0
votes

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"
0
votes

You can try a Measure like below-

your_expected_value = 

var current_row_data = MIN(your_table_name[DATA]) 
--I Guess the column name should be Date instead
var current_row_data_minus_90_day = MIN(your_table_name[DATA]) - 90 
var current_row_cd_produto = MIN(your_table_name[CD_PRODUTO])
var current_row_cd_loja = MIN(your_table_name[CD_LOJA])

RETURN
CALCULATE(
    SUM(your_table_name[QT_VENDA]),
    FILTER(
        ALL(your_table_name),
        your_table_name[DATA] >= current_row_data_minus_90_day 
        && your_table_name[DATA] <= current_row_data
        && your_table_name[CD_PRODUTO] = current_row_cd_produto
        && your_table_name[CD_LOJA] = current_row_cd_loja
    )
)/90

--Here 90 used for static 3 month consideration