Forum Discussion
Inventory forecast using calculation based on previous month data
I have three tables, all appended as one: Produced Goods, Pick up, and Inventory. Produced Goods and Pick up have values until Dec 2024, while Inventory has only Jan and Feb values. I need to forecast the rest of the year's inventory using the following formula:
Inventory Mar = Inventory Feb + Produced Goods Mar - Pick up Mar
so on and so forth...
Using DAX, is it possible to add rows to the Inventory with the calculated forecast?
If not, is there a work around? I have tried creating individual measures with values of individual months, but I can visualise all that in a table or matrix only. The many measures don't work well with line charts or the like.
Any help is appreciated. Thanks in advence!
5 Replies
Using DAX, is it possible to add rows to the Inventory with the calculated forecast?You cannot add rows to existing tables in DAX. You can only take away (filter).
A workaround would be to create a calculated table that implements your desired logic. Please note that this a static table that is computed once during semantic model refresh.
Hi,
Share data in a format that can be pasted in an MS Excel file and show the expected result.
Here you go:
Produced Goods Pick up Inventory Region Customer Product Jan Feb Mar Apr May Jan Feb Mar Apr May Jan Feb Mar Apr May R01 C01 P01 13 17.6 20 21 24 19 22 20 26 27 26 32.67 32.67 27.67 24.67 R01 C03 P02 4 4 4 4 3 22 24 28 32 35 R01 C04 P04 3 15 13 13 15 19 12 18 17 15 18 19 14 12 R01 C04 P05 4 1 20 26 27 10 2 8 31 11 9 29 47 43 R02 C06 P08 12 8 17 12 17 14 8 20 23 11 12 9 21 15 R02 C07 P08 3 0 3 3 R02 C02 P09 23 12 2.33 10 12 2.33 1 14 14 2 R02 C02 P10 4 8 10 8 6 10 18 10 10 R02 C02 P11 23 12 2.33 10 12 2.33 1 14 14 2 R02 C02 P12 5 0 0 0 0 5 So Inventory March onwards is:
Inventory March = Inventory Feb + Produced Goods March - Pick up March
So on and so forth
The data is in three separate tables appended as one, with their identifiers (first two columns plus a date column) in common and making each row unique.There's absolutely nothing i can understand there.
