Forum Discussion
Calculated columns based on past 3 months in power query/M
First time trying to use M in power query... what I have is this 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 the 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 ?
Hello Anonymous
this is best done in DAX to make the analysis. Here the formula for your measure
CALCULATE(sum(YourTable[QT_venda]),filter(ALLSELECTED(YourTable[DATA]),YourTable[DATA]>=TODAY()-3))/3and then put it in your visual like that
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
4 Replies
Jimmy801Community Champion
Hello Anonymous
could you please post some data, that we can use for your solution and the expected output with some more explanation?
I means why not filter your table that keeps data only of the last 3 months, then group by product and Loja and sum the venda column dividing by 92? The result you could then use to join your original table with your grouped table to visualize the calculated value on every row of your original data.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy"I means why not filter your table that keeps data only of the last 3 months, then group by product and Loja and sum the venda column dividing by 92? The result you could then use to join your original table with your grouped table to visualize the calculated value on every row of your original data. "
Makes sense, but how do I go about that using DAX or M ? About a more indepth explanationLet's say I want the past 3 days, from the model above the total sales of CD_Produto B and CD_Loja Z1 is 1 that divided by the count of today -3 days (3) returns 1/3.
Total QT_Venda from combination of A Z2 in the past 3 days is 3 (1 on day 27 other on 28th ) so divided by those 3 days results in a 1
As for the other values , since there is nor egistry of their sales in the past 3 days they return a 0.
- Jimmy801
Hello Anonymous
this is best done in DAX to make the analysis. Here the formula for your measure
CALCULATE(sum(YourTable[QT_venda]),filter(ALLSELECTED(YourTable[DATA]),YourTable[DATA]>=TODAY()-3))/3and then put it in your visual like that
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy