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
https://accuratio2015-my.sharepoint.com/:u:/g/personal/consultoria_accuratio2015_onmicrosoft_com/EQO...
DATASOURCE EXAMPLE
https://accuratio2015-my.sharepoint.com/:x:/g/personal/consultoria_accuratio2015_onmicrosoft_com/EdA...
