Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, please help me with this case
Scenario, I have a sales table. I need to create a column Forecast Quantity base on the sales quantity in the sales table and put it in Forecast table.
Target store_ID and target product_ID is in the forecast table (don't need to calculate for all transaction)
In SQL it would be something like this
SELECT SUM(QUANTITY) FROM W_SELLOUT_TRANSACTION_F AS A LEFT JOIN W_OPTIMAL_INVENTORY_F AS B ON A.W_STORE_WID = B.W_STORE_WID AND A.W_PRODUCT_WID = B.W_PRODUCT_WID WHERE A.W_DATE_WID < 20191231
AND A.W_DATE_WID >= 20190101)
What I have in mind is like this:
Sell Out Qty = SUMX(V_W_SELLOUT_TRANSACTION_F, CALCULATE(sum(V_W_SELLOUT_TRANSACTION_F[QUANTITY]), V_W_OPTIMAL_INVENTORY_F[W_STORE_WID]=V_W_SELLOUT_TRANSACTION_F[W_STORE_WID], V_W_OPTIMAL_INVENTORY_F[W_PRODUCT_WID]=V_W_SELLOUT_TRANSACTION_F[W_PRODUCT_WID], V_W_SELLOUT_TRANSACTION_F[W_DATE_WID]<V_W_OPTIMAL_INVENTORY_F[FROM_DATE_WID]))
create a concatenated field on W_STORE_WID and W_PRODUCT_WID and join tables W_SELLOUT_TRANSACTION_F W_OPTIMAL_INVENTORY_F
using that field.
Also, create a date dimension and join that with W_DATE_WID
Now you use date slicer or calculation in the formula to control your logic
Hi @amitchandak , The data is very big (25M rows transaction data) and I already try an SQL solution. I want to find a DAX equivalent solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |