Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Power BI formula needed

Please help in calculating the Day Level Order Balance Qty Note - Data is shorted by Order Date first then Order Qunatity   Material_Location_Order Booked Date Location Material Order Quanti...
Data Protection
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
1 year ago

I didn't see any other column which is similar to index column that we can use in the DAX. 

Let's see if any one else can provide better solution for you.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Ryan I have tried your formula, need further help, I calculated the Index for each unique value i.e. Inventory Key with below formula

I have useded your formula to calculate the Balance Qty, below is the formula.

Balance qty values are not what I was expecting, my desire outcome is column "Expected Output" in the below tabel which I have calculated manually.
Please help me to get the Expected Output value calculation formula

Inventory KeyDepotMaterialOrder Quantity(Item)Order Booked DateInventory KeyOpening InventorySales Order TotalIndexBalance QtyExpected Output
933001990099911031/8/202511039330019900999408-Jan-25933001990099911031/8/202535666.0036160290352
933001990099911031/8/2025110393300199009991608-Jan-25933001990099911031/8/202535666.0036160290336
933001990099911031/8/2025110393300199009991008-Jan-25933001990099911031/8/202535666.0036160290326
933001990099911031/8/202511039330019900999808-Jan-25933001990099911031/8/202535666.0036160290318
933001990099911031/8/202511039330019900999108-Jan-25933001990099911031/8/202535666.0036160290317
933001990099911031/8/202511039330019900999208-Jan-25933001990099911031/8/202535666.0036160290315
933001990099911031/8/202511039330019900999208-Jan-25933001990099911031/8/202535666.0036160290313
933001990099911031/8/202511039330019900999208-Jan-25933001990099911031/8/202535666.0036160290311
933001990099911031/8/202511039330019900999208-Jan-25933001990099911031/8/202535666.0036160290309
933001990099911031/8/202511039330019900999408-Jan-25933001990099911031/8/202535666.0036160290305
933001990099911031/8/202511039330019900999108-Jan-25933001990099911031/8/202535666.0036160290304
933001990099911031/8/202511039330019900999408-Jan-25933001990099911031/8/202535666.0036160290300
933001990099911031/8/2025110393300199009991008-Jan-25933001990099911031/8/202535666.0036160290290
933001990099911041/8/202511049330019900999408-Jan-25933001990099911041/8/2025472116.0036171356468
933001990099911041/8/202511049330019900999408-Jan-25933001990099911041/8/2025472116.0036171356464
933001990099911041/8/202511049330019900999408-Jan-25933001990099911041/8/2025472116.0036171356460
933001990099911041/8/2025110493300199009991208-Jan-25933001990099911041/8/2025472116.0036171356448
933001990099911041/8/202511049330019900999208-Jan-25933001990099911041/8/2025472116.0036171356446
933001990099911041/8/202511049330019900999608-Jan-25933001990099911041/8/2025472116.0036171356440
933001990099911041/8/202511049330019900999608-Jan-25933001990099911041/8/2025472116.0036171356434
933001990099911041/8/2025110493300199009994008-Jan-25933001990099911041/8/2025472116.0036171356394
933001990099911041/8/2025110493300199009992808-Jan-25933001990099911041/8/2025472116.0036171356366
933001990099911041/8/202511049330019900999208-Jan-25933001990099911041/8/2025472116.0036171356364
933001990099911041/8/202511049330019900999208-Jan-25933001990099911041/8/2025472116.0036171356362
933001990099911041/8/202511049330019900999608-Jan-25933001990099911041/8/2025472116.0036171356356