Forum Discussion

Tan_LC's avatar
Tan_LC
Icon for Helper II rankHelper II
4 years ago
Solved

Calculate Daily Balance Qty

Hi, in table below, in order to get the Daily Balance (kg), the calculation should be Stock Available (kg) deduct the Usage.

If the Stock Available (kg) is blank, then it should use the previous day balance to continue deduct the Usage until the Stock Available (kg) with value available. 

 

Kindly assist.

 

 

Thanks.

 

Regards,

TanLC

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Tan_LC , Sunch calculation should always done cumulative manner

     

    example measure

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date'))   - CALCULATE(SUM(Table[usage]),filter(date,date[date] <=maxx(date,date[date])))

    • Tan_LC's avatar
      Tan_LC
      Icon for Helper II rankHelper II
      4 years ago

      Dear Sir,

       

      Thanks on the feedback. However, error message pop up as below.

       

       

      Besides, I've further make clear on my desired outcome value as below using excel. Hope you can assist.

      Thanks.

       

       

      Regards,

      TanLC

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Share the table in a format that can be pasted in an MS Excel file.

  • Tan_LC's avatar
    Tan_LC
    Icon for Helper II rankHelper II
    4 years ago

    In below is the excel:

     

    Prod Date Usage  Stock Available (kg)  Daily Balance (kg) [Desired Outcome] 
    01/03/2022   1,097.68                  82,875.00                                                 81,763.24
    02/03/2022   1,111.76                  80,875.00                                                 79,750.41
    03/03/2022   1,124.59                                                  78,617.14
    04/03/2022   1,133.27                                                  77,438.31
    05/03/2022   1,178.83                                                  76,237.49
    06/03/2022   1,200.82                                                  75,114.50
    07/03/2022   1,122.99                                                  74,022.83
    08/03/2022   1,091.67                                                  72,951.64
    09/03/2022   1,071.19                                                  71,878.47
    10/03/2022   1,073.17                                                  70,749.43
    11/03/2022   1,129.04                                                  69,618.96
    12/03/2022   1,130.47                                                  68,480.08
    13/03/2022   1,138.88                                                  67,274.72
    14/03/2022   1,205.36                                                  66,025.74
    15/03/2022   1,248.98                                                  64,764.99
    16/03/2022   1,260.75                                                  63,506.29
    17/03/2022   1,258.70                  62,875.00                                                 61,590.05
    18/03/2022   1,284.95                  62,875.00                                                 61,639.29
    19/03/2022   1,235.71                                                  60,390.60
    20/03/2022   1,248.69                                                  59,149.89
    21/03/2022   1,240.71                                                  57,927.54
    22/03/2022   1,222.35                  53,875.00                                                 53,875.00

     

    Thanks.

     

    Regards,

    TanLC

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Please exlplain how you have arrived at the figures in the Desired outcome column.

      • Tan_LC's avatar
        Tan_LC
        Icon for Helper II rankHelper II
        4 years ago

        Ashish_Mathur ,

         

        Please refer column E for the calculation. Thanks.

         

        Column A Column B  Column C  Column D Column E
        Prod Date Usage  Stock Available (kg)  Daily Balance (kg) [Desired Outcome]  
        01/03/2022   1,097.68                  82,875.00                                                 81,763.24=C3-B4
        02/03/2022   1,111.76                  80,875.00                                                 79,750.41=C4-B5
        03/03/2022   1,124.59                                                  78,617.14=D4-B6
        04/03/2022   1,133.27                                                  77,438.31=D5-B7
        05/03/2022   1,178.83                                                  76,237.49=D6-B8
        06/03/2022   1,200.82                                                  75,114.50=D7-B9
        07/03/2022   1,122.99                                                  74,022.83=D8-B10
        08/03/2022   1,091.67                                                  72,951.64=D9-B11
        09/03/2022   1,071.19                                                  71,878.47=D10-B12
        10/03/2022   1,073.17                                                  70,749.43=D11-B13
        11/03/2022   1,129.04                                                  69,618.96=D12-B14
        12/03/2022   1,130.47                                                  68,480.08=D13-B15
        13/03/2022   1,138.88                                                  67,274.72=D14-B16
        14/03/2022   1,205.36                                                  66,025.74=D15-B17
        15/03/2022   1,248.98                                                  64,764.99=D16-B18
        16/03/2022   1,260.75                                                  63,506.29=D17-B19
        17/03/2022   1,258.70                  62,875.00                                                 61,590.05=C19-B20
        18/03/2022   1,284.95                  62,875.00                                                 61,639.29=C20-B21
        19/03/2022   1,235.71                                                  60,390.60=D20-B22
        20/03/2022   1,248.69                                                  59,149.89=D21-B23
        21/03/2022   1,240.71                                                  57,927.54=D22-B24
        22/03/2022   1,222.35                  53,875.00                                                 53,875.00=C24-B25

         

        Regards,

        TanLC