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

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, Anonymous 

 

As ryan_mayu  says, you can't get the results you expect without indexed columns. Your data has no unique recursive values, and using Dax to calculate indexed columns doesn't quite work. You need to add the index column to the power query.

 

Best Regards,

Community Support Team _Charlotte

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