Forum Discussion

Mathewtmp's avatar
Mathewtmp
Frequent Visitor
1 year ago
Solved

Help to create a inventory projection report

Hi all, Beginner here and trying to create a inventory projection report. I have read a lot of related posts but have been unsuccessful in replicating the formulas.    My database has a tables fo...
  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    Hi Mathewtmp ,

    Here is your solution

    Steps to perform

    1. create an Union table as below

    Union table =
    ADDCOLUMNS(
        DISTINCT(
                UNION(
                    SELECTCOLUMNS(Demand,Demand[Date],Demand[Part No]),
                    SELECTCOLUMNS(SIT,SIT[Date],SIT[Part No]),
                    SELECTCOLUMNS(SOH,SOH[Date],SOH[Part No])
                )
        ),
        "Demand",LOOKUPVALUE(Demand[Demand],Demand[Date],Demand[Date],Demand[Part No],Demand[Part No]),
        "SOH",LOOKUPVALUE(SOH[Qty],SOH[Date],Demand[Date],SOH[Part No],Demand[Part No]),
        "SIT",LOOKUPVALUE(SIT[PO Qty],SIT[Date],Demand[Date],SIT[Part No],Demand[Part No])
    )
     
    2. Create a Measure
    Daily Balance =
     sum('Union table'[SOH])+sum('Union table'[SIT])-sum('Union table'[Demand])
    3. Create another measure
    Opening Balance =
    sum('Union table'[SOH])+
    CALCULATE([Daily Balance],ALLEXCEPT('Union table','Union table'[Demand_Part No]),'Union table'[Demand_Date]<max('Union table'[Demand_Date]))
    4. Create another Measure
    Stock Balance =
     CALCULATE([Daily Balance],ALLEXCEPT('Union table','Union table'[Demand_Part No]),'Union table'[Demand_Date]<=max('Union table'[Demand_Date]))
     
    Now take a matrix, columns as below

    Please note, all the columns will come from the new union table. 

    In your sample data, the incoming dates are coinsiding with demand dates. So I used demand date as axis which is simple. If it is not the case in your actual table, you need to use a date master. 

     

     

    thanks

    If this solves your issue, please accept as solution.

Mathewtmp's avatar
Mathewtmp
Frequent Visitor
1 year ago

Hi Rupak,

 

Thanks for replying. I am  assuming you meant the tables in the database?

If so please see below the the same as text.

Table SOH

DatePart NoQty
17/02/2025A111000
17/02/2025O11500

 

Table SIT

DatePart NoPO Qty
10/03/2025A11250
14/04/2025A11250
19/05/2025A11250
24/02/2025O11125
3/03/2025O11125
7/04/2025O11125
12/05/2025O11125
9/06/2025O11125

 

Table Demand

DatePart NoDemand
17/02/2025A11250
24/02/2025A11250
3/03/2025A11250
10/03/2025A11250
17/03/2025A11250
24/03/2025A11250
31/03/2025A11250
7/04/2025A11250
14/04/2025A11250
21/04/2025A11250
28/04/2025A11250
5/05/2025A11250
12/05/2025A11250
19/05/2025A11250
26/05/2025A11250
2/06/2025A11250
9/06/2025A11250
17/02/2025O11125
24/02/2025O11125
3/03/2025O11125
10/03/2025O11125
17/03/2025O11125
24/03/2025O11125
31/03/2025O11125
7/04/2025O11125
14/04/2025O11125
21/04/2025O11125
28/04/2025O11125
5/05/2025O11125
12/05/2025O11125
19/05/2025O11125
26/05/2025O11125
2/06/2025O11125
9/06/2025O11125

 

Table Part

Part NoPart Name
A11Apples
O11Oranges

 

Rupak_bi's avatar
Rupak_bi
Icon for Super User rankSuper User
1 year ago

Hi Mathewtmp ,

Here is your solution

Steps to perform

1. create an Union table as below

Union table =
ADDCOLUMNS(
    DISTINCT(
            UNION(
                SELECTCOLUMNS(Demand,Demand[Date],Demand[Part No]),
                SELECTCOLUMNS(SIT,SIT[Date],SIT[Part No]),
                SELECTCOLUMNS(SOH,SOH[Date],SOH[Part No])
            )
    ),
    "Demand",LOOKUPVALUE(Demand[Demand],Demand[Date],Demand[Date],Demand[Part No],Demand[Part No]),
    "SOH",LOOKUPVALUE(SOH[Qty],SOH[Date],Demand[Date],SOH[Part No],Demand[Part No]),
    "SIT",LOOKUPVALUE(SIT[PO Qty],SIT[Date],Demand[Date],SIT[Part No],Demand[Part No])
)
 
2. Create a Measure
Daily Balance =
 sum('Union table'[SOH])+sum('Union table'[SIT])-sum('Union table'[Demand])
3. Create another measure
Opening Balance =
sum('Union table'[SOH])+
CALCULATE([Daily Balance],ALLEXCEPT('Union table','Union table'[Demand_Part No]),'Union table'[Demand_Date]<max('Union table'[Demand_Date]))
4. Create another Measure
Stock Balance =
 CALCULATE([Daily Balance],ALLEXCEPT('Union table','Union table'[Demand_Part No]),'Union table'[Demand_Date]<=max('Union table'[Demand_Date]))
 
Now take a matrix, columns as below

Please note, all the columns will come from the new union table. 

In your sample data, the incoming dates are coinsiding with demand dates. So I used demand date as axis which is simple. If it is not the case in your actual table, you need to use a date master. 

 

 

thanks

If this solves your issue, please accept as solution.

  • Mathewtmp's avatar
    Mathewtmp
    Frequent Visitor
    1 year ago

    Hi Rupak_bi ,

    Thank you so much for your help with the solution. It works perfectly. 🙂

    Much appreciated. 

    Cheers,

    Mat

  • CampBI's avatar
    CampBI
    Icon for Helper I rankHelper I
    1 year ago

    Hi Rupak_bi  I find your solution very helpful with my current scenario. I would like also to ask how could I get the measure of my SOH stock on hand when my data for Current Stock is Data every end of the month, since the inventory will be the end of month I don't have any date table for that.  

    My visual should show in weekly and month (hierarchy), see photo for current visual.

     

    _________

    Data:

    Inventory(Count per item every end of month) + Arrival(orders from the vendor with date) - Reserved(for production with date) - Sales Order(all items with date)

    ________

     

    Inventory + Jan Arrival - Reserved - SO = Forecast

    Forecast Jan + Feb Arrival - Reserved - SO = Forecast

    Forecast Feb + Mar Arrival - Reserved - SO = Forecast

     

    Thank you.

    Rupak_bi

  • CampBI's avatar
    CampBI
    Icon for Helper I rankHelper I
    1 year ago

    Hi Rupak_bi , I find your solution helpful with my current data scenario. But I dont have date for my Stock on hand (SOH) our inventory for stock on hand is counted at the end of the month.

     

    This is my current visual how could I get the line connected to each other where my data for each legend is in different table.

    Date table (created date table)

    Item No column and Invenory count (unique distinct table, with coulmn count of inventory, which is counted end of the month)

    For Reserved (table 1) (production)

    For Arrival (table 2) (stock to be arrived)

    For Sales Order (table 3) (Order items with date)

    For Forecast it is just a measure where , Sum of Inventory + Arrival - Reserved - Sales Order

     

    Current problem is the line is broken since the data comes from different table each category

    Inventory is end of month only count

     

    Thank you.

     

  • CampBI's avatar
    CampBI
    Icon for Helper I rankHelper I
    1 year ago

    Hi Rupak, I find your solution helpful with my current data scenario. But I dont have date for my Stock on hand (SOH) our inventory for stock on hand is counted at the end of the month.

     

    This is my current visual how could I get the line connected to each other where my data for each legend is in different table.

    Date table (created date table)

    Item No column and Invenory count (unique distinct table, with coulmn count of inventory, which is counted end of the month)

    For Reserved (table 1) (production)

    For Arrival (table 2) (stock to be arrived)

    For Sales Order (table 3) (Order items with date)

    For Forecast it is just a measure where , Sum of Inventory + Arrival - Reserved - Sales Order

     

    Current problem is the line is broken since the data comes from different table each category

    Inventory is end of month only count

     

    Thank you.