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

Anonymous 

you can try this

 

1. create an index column in PQ

 

2. use DAX to create the column

 

Column = 'Table'[Opening Inventory]-sumx(FILTER('Table','Table'[Order Date]=EARLIER('Table'[Order Date])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Order Quantity])
 
 
pls see the attachment below
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
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    You will need to add an index column if you want it to be evaluated for each row. We cannot use the order date and quantity column as the values repeat. And also, with that much row, I dont think a calculated column is a good option - it's computationally expensive.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Material_Location_Order Booked Date is the key that I want to use to calculate the Day Level Order Balance Qty in Power BI not in Power Query 

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Your sample data has repeating values for those columns so if the calculation were to be for each row in the sample data, there needs to be a column that identifies the row thus the use of an index column. But even if there already is an index column, creating a calculated column with that much rows would be computationally expensive.