Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Material Arrive Po qauntity calculation

Hi All,
I have two exceel sheets in which on hand qty is from Sheet A and Arrive PO Qty is from another sheet based on date.
my week is starting from week 9th - on hand qty numbers to be get from the on hand qty in excel sheet A (static value).
Arrive PO qty to be got from the other excel sheet according to week number. Dynamic usage that can be changed weekly.
Balance for week 9 is static on hand qty + arrive qty - usage (for a particular item) and this balance from week 9 is same as on hand qty in week 10.
On hand qty starts becoming dynamic from week 10 and But initally it is static from week 9
On hand qty + arrive PO qty - usage = balance is our standard operation happening every week to get balance
Balance from previous week is same as on hand qty for next week.

sheet A data

Item On Hand Qty
001 0598 36 97500
001 0599 36 97450
001 0851 36 97321
002 1715 9J 97356
009 0331 32368
009 0357 34578
012 9114 22876
012 9116 22435
045 0964 22265
050 0035 22579
066 0196 22356
066 0197 22267
080 0168 22 97211
080 0168 36 97344



Sheet B Data

NameAvailableQTYWeeklyUsage
543 0106 3612-04-20241,000200
001 0598 36 9713-04-20241,000200
001 0598 36 9714-04-20241,500200
001 0599 36 9727-02-2024240300
001 0599 36 9727-02-20241,200300
001 0599 36 9725-04-20241,000200
001 0851 36 9727-02-2024500300
001 0851 36 9712-04-20242,000100
001 0851 36 9725-04-20242,000100
001 0851 36 9725-04-20243,879100
001 0851 36 9709-05-20241,000200
001 0851 36 9709-05-20241,000200
001 0851 36 9709-05-20241,000200
001 0851 36 9706-09-20241,689400
002 1710 9J 9729-04-20242,240400
002 1715 9J 9704-03-2024252200
002 1715 9J 9727-05-20241,824100
002 1715 9J 9703-06-20242,052250
003 1268 3623-04-20246,480350
003 1268 3624-05-2024950400
003 1268 3605-07-202422150
003 1268 3619-07-20242,160200
009 0331 3229-04-20244,000100
009 0331 3216-05-20246,000200
009 0331 3217-06-20244,000300
009 0331 3225-10-20244,000400
009 0357 3429-04-20241,925200
009 0357 3417-06-20241,495150
009 0357 3409-08-20241,300250
009 0357 3430-08-20241,240200
009 0357 3430-09-20241,420300
009 0357 3414-10-20241,337400
012 9114 2212-04-20242,000100
012 9114 2212-04-202489100
012 9114 2207-08-20242,000200
012 9116 2226-02-20240250
012 9116 2227-02-2024349250
012 9116 2212-04-20241,800150
045 0964 2227-02-2024800100
045 0964 2218-03-20241,671200
045 0964 2215-04-20242,000300
045 0964 2215-04-20242,000300
045 0964 2222-04-20243,000250
045 0964 2224-07-20243,100450
045 0964 2230-07-20242,300350
050 0035 2208-04-2024500100
050 0035 2223-04-2024500200
050 0035 2223-04-2024958200
050 0035 2206-09-2024547300
050 0035 3627-02-2024500200
050 0035 3622-03-202450300
050 0035 3608-04-20241,000150
050 0035 3615-04-20241,000250
050 0035 3627-05-20241,000350
050 0035 3607-06-20241,000400
066 0196 2227-02-202452200
066 0196 2227-02-2024500200
066 0196 2231-05-20241,000300
066 0196 2202-08-20241,000250
066 0197 2231-05-20241,000200
066 0197 2212-07-20241,000300
080 0168 22 9726-04-2024300150
080 0168 22 9710-05-2024500250
080 0168 22 9720-05-2024200350




how to Acheive the same in Power bi??

 

amitchandak 

Thanks in advance!!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    Start by importing your Excel sheets into Power BI.

    Make sure there's a common column between Sheet A and Sheet B that you can use to create relationships in Power BI.

     

    Create a calculated column or measure to calculate the balance. 

    Balance = SUM(TableA[On Hand Qty])+ SUM(TableB[QTY])-SUM(TableB[WeeklyUsage])

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    Start by importing your Excel sheets into Power BI.

    Make sure there's a common column between Sheet A and Sheet B that you can use to create relationships in Power BI.

     

    Create a calculated column or measure to calculate the balance. 

    Balance = SUM(TableA[On Hand Qty])+ SUM(TableB[QTY])-SUM(TableB[WeeklyUsage])

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.