Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear PBI friends
I am struggling with problem for a weeks
Need to calculate prediction sales in value (€) according average price previous 3 months
See attached table ( data table - left , desired table -right)
How work with such averages in dax or /and query.
Excel file for better understanding
Thanks in advance
Here is the data table
Month | Product | Sales € | Sales pcs | AVG price | Sales p3m € | Sales p3m pcs | AVG price p3m (€/pcs) | Plan pcs | |
2023-01-01 | Product A | 100 | 200 | 0,50 | 0 | 0 | 0,00 | ||
2023-02-01 | Product A | 300 | 200 | 1,50 | 100 | 200 | 0,50 | ||
2023-03-01 | Product A | 400 | 300 | 1,33 | 400 | 400 | 1,00 | ||
2023-04-01 | Product A | 300 | 500 | 0,60 | 800 | 700 | 1,14 | ||
2023-05-01 | Product A | 200 | 300 | 0,67 | 1000 | 1000 | 1,00 | ||
2023-06-01 | Product A | 900 | 1100 | 0,82 | 200 | ||||
2023-07-01 | Product A | 0,82 | 100 | ||||||
2023-08-01 | Product A | 0,82 | 150 | ||||||
2023-09-01 | Product A | 0,82 | 300 | ||||||
2023-10-01 | Product A | 0,82 | 400 | ||||||
2023-11-01 | Product A | 0,82 | 200 | ||||||
2023-12-01 | Product A | 0,82 | 100 | ||||||
2023-01-01 | Product B | 200 | 400 | 0,50 | 0 | 0 | 0,00 | ||
2023-02-01 | Product B | 200 | 300 | 0,67 | 200 | 400 | 0,50 | ||
2023-03-01 | Product B | 300 | 500 | 0,60 | 400 | 700 | 0,57 | ||
2023-04-01 | Product B | 100 | 100 | 1,00 | 700 | 1200 | 0,58 | ||
2023-05-01 | Product B | 300 | 400 | 0,75 | 600 | 900 | 0,67 | ||
2023-06-01 | Product B | 700 | 1000 | 0,70 | 200 | ||||
2023-07-01 | Product B | 0,70 | 300 | ||||||
2023-08-01 | Product B | 0,70 | 150 | ||||||
2023-09-01 | Product B | 0,70 | 300 | ||||||
2023-10-01 | Product B | 0,70 | 400 | ||||||
2023-11-01 | Product B | 0,70 | 200 | ||||||
2023-12-01 | Product B | 0,70 | 100 |
Hi,
Share the download link of the MS Excel file with your formulas written there so that the logic can be understood.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |