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
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

 

 

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

do not use DAX to create an index column ,just create it in the power query

 

Column = 'Table'[Opening Inventory]- sumx(FILTER('Table','Table'[Depot]=EARLIER('Table'[Depot])&&'Table'[Index.1]<=EARLIER('Table'[Index.1])),'Table'[Order Quantity(Item)])
 
pls see the attachment below