Inventory forecast using calculation based on previous month data

[Anonymous]

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!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[Anonymous]

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.

[Ashish_Mathur]

There's absolutely nothing i can understand there.

[Anonymous]

PG = Produced Goods
PU = Pickup
IN = Inventory

Region	Customer	Product	PG Feb	PG Mar	PU Feb	PU Mar	IN Feb	IN Mar
R01	C01	P01	17	20	22	18	32	24
R02	C02	P02	6	20	10	12	12	10
R03	C03	P03	4	4	2	3	5	6
R04	C04	P04	15	13	5	6	6	13

IN Mar = IN Feb + PG Mar - PU Mar

The identifiers, Region, Customer, and Product, are similar throughout the tables. Any help is appreciated. Thanks.

[lbendlin]

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.