Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Leihan_55
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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Here you go:

 Produced GoodsPick upInventory
RegionCustomerProductJanFebMarAprMayJanFebMarAprMayJanFebMarAprMay
R01C01P011317.620212419222026272632.6732.6727.6724.67
R01C03P0244443     2224283235
R01C04P04315131315 191218171518191412
R01C04P0541202627102 831119294743
R02C06P0812817121714820 23111292115
R02C07P08   3        033
R02C02P09  23  122.3310 122.33114142
R02C02P10 48   10 8 610181010
R02C02P11  23  122.3310 122.33114142
R02C02P12    5     00005

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

PG = Produced Goods
PU = Pickup
IN = Inventory

RegionCustomerProductPG FebPG MarPU FebPU MarIN FebIN Mar
R01C01P01172022183224
R02C02P0262010121210
R03C03P03442356
R04C04P04151356613


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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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