Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'm trying to calculate cumulative inflation, month by month for unit price forecast (DAX not Power Query).
I need two columns:
- one column with cumuative_inflation (I tried with "Productx", but it didn´t work.)
- another column with Unit_Price_Forecast (last unit_price available in the sales table)*(cumulative_inflation)
Can you help me?
FORECAST_TABLE
DATE | BU | SKU | INFLATION | Q |
1/9/2023 | PRO | 34124600 | 1 | 37665 |
1/9/2023 | RHCE | 19017003 | 1 | 17640 |
1/9/2023 | RHCE | 19017103 | 1 | 25000 |
1/9/2023 | RHCN | 19017003 | 1,06 | 291268 |
1/9/2023 | RHCN | 19017103 | 1,06 | 246497 |
1/10/2023 | PRO | 34124600 | 1 | 33480 |
1/10/2023 | RHCE | 19017003 | 1 | 17640 |
1/10/2023 | RHCE | 19017103 | 1 | 25000 |
1/10/2023 | RHCN | 19017003 | 1,06 | 300006 |
1/10/2023 | RHCN | 19017103 | 1,06 | 253891 |
1/11/2023 | PRO | 34124600 | 1 | 41850 |
1/11/2023 | RHCE | 19017003 | 1 | 17640 |
1/11/2023 | RHCE | 19017103 | 1 | 25000 |
1/11/2023 | RHCN | 19017003 | 1 | 285006 |
1/11/2023 | RHCN | 19017103 | 1 | 241197 |
1/12/2023 | PRO | 34124600 | 1 | 50219 |
1/12/2023 | RHCN | 19017003 | 1,15 | 282156 |
1/12/2023 | RHCN | 19017103 | 1,15 | 238785 |
1/1/2024 | PRO | 34124600 | 1 | 34875 |
1/1/2024 | RHCE | 19017003 | 1,08 | 21600 |
1/1/2024 | RHCE | 19017103 | 1,08 | 32400 |
1/1/2024 | RHCN | 19017003 | 1,05 | 297897 |
1/1/2024 | RHCN | 19017103 | 1,05 | 223526 |
SALES TABLE
DATE | BU | SKU | UNIT_PRICE | Q |
1/8/2023 | PRO | 34124600 | ||
1/8/2023 | RHCE | 19017003 | $ 10,0 | 15876 |
1/8/2023 | RHCE | 19017103 | $ 9,0 | 22500 |
1/8/2023 | RHCN | 19017003 | $ 1.000,0 | 262141,2 |
1/8/2023 | RHCN | 19017103 | $ 1.500,0 | 221847,3 |
1/7/2023 | PRO | 34124600 | $ 11,0 | 33480 |
1/7/2023 | RHCE | 19017003 | $ 9,5 | 17640 |
1/7/2023 | RHCE | 19017103 | $ 8,5 | 25000 |
1/7/2023 | RHCN | 19017003 | $ 950,0 | 300006 |
1/7/2023 | RHCN | 19017103 | $ 1.200,0 | 253891 |
1/6/2023 | PRO | 34124600 | $ 10,5 | 41850 |
1/6/2023 | RHCE | 19017003 | $ 9,0 | 17640 |
1/6/2023 | RHCE | 19017103 | $ 8,1 | 25000 |
1/6/2023 | RHCN | 19017003 | $ 902,5 | 285006 |
1/6/2023 | RHCN | 19017103 | $ 1.140,0 | 241197 |
I expect something like that:
DATE | BU | SKU | INFLATION | Q | INFLAT_ACUMUL | UNIT_PRICE_FORECAST |
1/9/2023 | PRO | 34124600 | 1 | 37665 | 1,00 | $ 11,0 |
1/9/2023 | RHCE | 19017003 | 1 | 17640 | 1,00 | $ 10,0 |
1/9/2023 | RHCE | 19017103 | 1 | 25000 | 1,00 | $ 9,0 |
1/9/2023 | RHCN | 19017003 | 1,06 | 291268 | 1,06 | $ 1.060,0 |
1/9/2023 | RHCN | 19017103 | 1,06 | 246497 | 1,06 | $ 1.590,0 |
1/10/2023 | PRO | 34124600 | 1 | 33480 | 1,00 | $ 11,0 |
1/10/2023 | RHCE | 19017003 | 1 | 17640 | 1,00 | $ 10,0 |
1/10/2023 | RHCE | 19017103 | 1 | 25000 | 1,00 | $ 9,0 |
1/10/2023 | RHCN | 19017003 | 1,06 | 300006 | 1,12 | $ 1.123,6 |
1/10/2023 | RHCN | 19017103 | 1,06 | 253891 | 1,12 | $ 1.685,4 |
1/11/2023 | PRO | 34124600 | 1 | 41850 | 1,00 | $ 11,0 |
1/11/2023 | RHCE | 19017003 | 1 | 17640 | 1,00 | $ 10,0 |
1/11/2023 | RHCE | 19017103 | 1 | 25000 | 1,00 | $ 9,0 |
1/11/2023 | RHCN | 19017003 | 1 | 285006 | 1,12 | $ 1.123,6 |
1/11/2023 | RHCN | 19017103 | 1 | 241197 | 1,12 | $ 1.685,4 |
1/12/2023 | PRO | 34124600 | 1 | 50219 | 1,00 | $ 11,0 |
1/12/2023 | RHCN | 19017003 | 1,15 | 282156 | 1,29 | $ 1.292,1 |
1/12/2023 | RHCN | 19017103 | 1,15 | 238785 | 1,29 | $ 1.938,2 |
1/1/2024 | PRO | 34124600 | 1 | 34875 | 1,00 | $ 11,0 |
1/1/2024 | RHCE | 19017003 | 1,08 | 21600 | 1,08 | $ 10,8 |
1/1/2024 | RHCE | 19017103 | 1,08 | 32400 | 1,08 | $ 9,7 |
1/1/2024 | RHCN | 19017003 | 1,05 | 297897 | 1,36 | $ 1.356,7 |
1/1/2024 | RHCN | 19017103 | 1,05 | 223526 | 1,36 | $ 2.035,1 |
Thanks, Ramiro
PBIX EXAMPLE
DATASOURCE EXAMPLE
thanks amitchandak, but still the same, take a look:
@Pepe1234 , Try with a common date table
Cumulative_Inflation =
CALCULATE(
PRODUCTX(FORECAST_TABLE, 1 + [INFLATION]),
FILTER(all(Date), Date[DATE] <= max(DAte[DATE]))
)
Unit_Price_Forecast =
VAR LatestPrice = calculate(Max(SALES_TABLE[UNIT_PRICE]), FILTER(all(Date), Date[DATE] = max(DAte[DATE])))
RETURN
[Cumulative_Inflation] * LatestPrice
I expected columns in yellow:
BR
Ramiro