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

Thank you Ryan
Data set is huge around 11-13 lakh row, is there any way I can get the same result without using the Index

 

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

     

     

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