Forum Discussion

Hinnantj's avatar
Hinnantj
Frequent Visitor
2 years ago

Running Balance - Reset to Zero

Hi! I am trying to get a running/remaing balance for each date based on the item, taking into account the monthly reduction and production. In cases where the balance is below zero, I only want to display zero, and the next month to start the balance from zero.

 

The table below is the data - with the "Remaining Balance" as my end result I am trying to achieve. 

ItemMonth_Year Quarter  Remaining_Balance  Monthly_Reduction  Beginning Balance (1st Month Only)  Production_Planned  Production_Unplanned 
Jeans12/1/2023 2024  Q1                                33,768                               10,417                                                                 44,185                                          -                                                  -  
Jeans1/1/2024 2024  Q2                                23,925                               10,417                                                                          -                                        574                                                -  
Jeans2/1/2024 2024  Q2                                23,834                               10,417                                                                          -                                  10,326                                                -  
Jeans3/1/2024 2024  Q2                                15,091                               10,417                                                                          -                                     1,674                                                -  
Jeans4/1/2024 2024  Q3                                26,067                               10,417                                                                          -                                  21,393                                                -  
Jeans5/1/2024 2024  Q3                                26,257                               10,417                                                                          -                                  10,607                                                -  
Jeans6/1/2024 2024  Q3                                15,840                               10,417                                                                          -                                            -                                                  -  
Jeans7/1/2024 2024  Q4                                  6,927                               10,417                                                                          -                                     1,504                                                -  
Jeans8/1/2024 2024  Q4                                         -                                 10,417                                                                          -                                            -                                                  -  
Jeans9/1/2024 2024  Q4                                         -                                 10,417                                                                          -                                            -                                                  -  
Jeans10/1/2024 2025  Q1                                         -                                 10,417                                                                          -                                            -                                                  -  
Jeans11/1/2024 2025  Q1                                         -                                 10,417                                                                          -                                            -                                                  -  
Jeans12/1/2024 2025  Q1                                15,689                               10,417                                                                          -                                  26,106                                                -  
Jeans1/1/2025 2025  Q2                                44,643                               10,417                                                                          -                                  39,371                                                -  
Jeans2/1/2025 2025  Q2                                71,678                               10,417                                                                          -                                  37,452                                                -  
Shirts12/1/2023 2024  Q1                             155,555                                        -                                                                 155,555                                          -                                                  -  
Shirts1/1/2024 2024  Q2                             155,555                                        -                                                                            -                                            -                                                  -  
Shirts2/1/2024 2024  Q2                             173,555                                        -                                                                            -                                  18,000                                                -  
Shirts3/1/2024 2024  Q2                             213,555                                        -                                                                            -                                            -  40000
Shirts4/1/2024 2024  Q3                             287,189                                        -                                                                            -                                  40,00033634
Shirts5/1/2024 2024  Q3                             327,189                                        -                                                                            -                                  40,000                                                -  
Shirts6/1/2024 2024  Q3                             367,189                                        -                                                                            -                                  40,000                                                -  
Shirts7/1/2024 2024  Q4                             407,189                                        -                                                                            -                                  40,000                                                -  
Shirts8/1/2024 2024  Q4                             457,189                                        -                                                                            -                                  50,000                                                -  
Shirts9/1/2024 2024  Q4                             497,189                                        -                                                                            -                                  40,000                                                -  
Shirts10/1/2024 2025  Q1                             617,189                                        -                                                                            -                                120,000                                                -  
Shirts11/1/2024 2025  Q1                             657,189                                        -                                                                            -                                  40,000                                                -  
Shirts12/1/2024 2025  Q1                             697,189                                        -                                                                            -                                  40,000                                                -  
Shirts1/1/2025 2025  Q2                             787,089                                        -                                                                            -                                  89,900                                                -  
Shirts2/1/2025 2025  Q2                             903,624                                        -                                                                            -                                116,535                                                -  

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Hinnantj ,

    You can create two measures as below to get it, please find the details in the attachment.

    Measure = 
    VAR CurrentItem = SELECTEDVALUE ( 'Table'[Item] )
    VAR CurrentDate = SELECTEDVALUE ( 'Table'[Month_Year] )
    VAR BeginningBalance = CALCULATE (
        SUM ( 'Table'[Beginning Balance (1st Month Only)] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem
    )
    VAR MonthlyReduction = CALCULATE (
        SUM ( 'Table'[Monthly_Reduction] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem,
        'Table'[Month_Year] <= CurrentDate
    )
    VAR Production = CALCULATE (
        SUM ( 'Table'[Production_Planned] ) + SUM ( 'Table'[Production_Unplanned] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem,
        'Table'[Month_Year] <= CurrentDate
    )
    RETURN
        BeginningBalance - MonthlyReduction + Production  
    Remaining_Balance = 
    VAR CurrentItem = SELECTEDVALUE ( 'Table'[Item] )
    VAR CurrentDate = SELECTEDVALUE ( 'Table'[Month_Year] )
    VAR MaxZDate= CALCULATE(MAX('Table'[Month_Year]),FILTER(    ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem&&[Measure]<0
    ))
    VAR MonthlyReduction2=CALCULATE (
        SUM ( 'Table'[Monthly_Reduction] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem&&
        'Table'[Month_Year] >= MaxZDate&&'Table'[Month_Year] <=CurrentDate
    )
    VAR Production2 = CALCULATE (
        SUM ( 'Table'[Production_Planned] ) + SUM ( 'Table'[Production_Unplanned] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Item] = CurrentItem&&
        'Table'[Month_Year] >= MaxZDate&&'Table'[Month_Year] <=CurrentDate
    )
    RETURN IF(NOT(ISBLANK(MaxZDate))&&CurrentDate>=MaxZDate,Production2-MonthlyReduction2,IF([Measure]<0,BLANK(),[Measure])) 

    Best Regards

    • Hinnantj's avatar
      Hinnantj
      Frequent Visitor

      Hello! Unfortunately, the results are not as expected. The balance is not reseting to zero upon after going negative. An updated sample data file can be found here SampleDate_Revised 

       

      If you upload and filter on items "B744" and "D127", you will see the Remaining_Balance measure does not reset as expected (shown in "Expected" column). 

       

       

       

      • Hinnantj's avatar
        Hinnantj
        Frequent Visitor

        Anonymous - Hi! Have you had a chance to review latest comments?