cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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 5
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

There's absolutely nothing i can understand there.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User
``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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors