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 DateLocationMaterialOrder QuantityOrder DateSales Order TotalOpening InventoryDay Level Order Balance QtyFormula
933001990099911084566411089330019900999207-Jan-25461816=G2-D2
9330019900999110845664110893300199009992007-Jan-254618-4=H2-D3
9330019900999110845664110893300199009992407-Jan-254618-28=H3-D4
933001990099911084566511089330019900999108-Jan-251487069=G5-D5
933001990099911084566511089330019900999108-Jan-251487068=H5-D6
933001990099911084566511089330019900999208-Jan-251487066=H6-D7
933001990099911084566511089330019900999208-Jan-251487064=H7-D8
933001990099911084566511089330019900999208-Jan-251487062=H8-D9
933001990099911084566511089330019900999208-Jan-251487060=H9-D10
933001990099911084566511089330019900999308-Jan-251487057=H10-D11
933001990099911084566511089330019900999308-Jan-251487054=H11-D12
933001990099911084566511089330019900999408-Jan-251487050=H12-D13
933001990099911084566511089330019900999408-Jan-251487046=H13-D14
933001990099911084566511089330019900999408-Jan-251487042=H14-D15
933001990099911084566511089330019900999608-Jan-251487036=H15-D16
933001990099911084566511089330019900999608-Jan-251487030=H16-D17
933001990099911084566511089330019900999608-Jan-251487024=H17-D18
933001990099911084566511089330019900999808-Jan-251487016=H18-D19
9330019900999110845665110893300199009991008-Jan-25148706=H19-D20
9330019900999110845665110893300199009991008-Jan-2514870-4=H20-D21
9330019900999110845665110893300199009991008-Jan-2514870-14=H21-D22
9330019900999110845665110893300199009992408-Jan-2514870-38=H22-D23
9330019900999110845665110893300199009994008-Jan-2514870-78=H23-D24
Data Protection

10 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super 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
        Super 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.