Forum Discussion
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.
| Item | Month_Year | Quarter | Remaining_Balance | Monthly_Reduction | Beginning Balance (1st Month Only) | Production_Planned | Production_Unplanned |
| Jeans | 12/1/2023 | 2024 Q1 | 33,768 | 10,417 | 44,185 | - | - |
| Jeans | 1/1/2024 | 2024 Q2 | 23,925 | 10,417 | - | 574 | - |
| Jeans | 2/1/2024 | 2024 Q2 | 23,834 | 10,417 | - | 10,326 | - |
| Jeans | 3/1/2024 | 2024 Q2 | 15,091 | 10,417 | - | 1,674 | - |
| Jeans | 4/1/2024 | 2024 Q3 | 26,067 | 10,417 | - | 21,393 | - |
| Jeans | 5/1/2024 | 2024 Q3 | 26,257 | 10,417 | - | 10,607 | - |
| Jeans | 6/1/2024 | 2024 Q3 | 15,840 | 10,417 | - | - | - |
| Jeans | 7/1/2024 | 2024 Q4 | 6,927 | 10,417 | - | 1,504 | - |
| Jeans | 8/1/2024 | 2024 Q4 | - | 10,417 | - | - | - |
| Jeans | 9/1/2024 | 2024 Q4 | - | 10,417 | - | - | - |
| Jeans | 10/1/2024 | 2025 Q1 | - | 10,417 | - | - | - |
| Jeans | 11/1/2024 | 2025 Q1 | - | 10,417 | - | - | - |
| Jeans | 12/1/2024 | 2025 Q1 | 15,689 | 10,417 | - | 26,106 | - |
| Jeans | 1/1/2025 | 2025 Q2 | 44,643 | 10,417 | - | 39,371 | - |
| Jeans | 2/1/2025 | 2025 Q2 | 71,678 | 10,417 | - | 37,452 | - |
| Shirts | 12/1/2023 | 2024 Q1 | 155,555 | - | 155,555 | - | - |
| Shirts | 1/1/2024 | 2024 Q2 | 155,555 | - | - | - | - |
| Shirts | 2/1/2024 | 2024 Q2 | 173,555 | - | - | 18,000 | - |
| Shirts | 3/1/2024 | 2024 Q2 | 213,555 | - | - | - | 40000 |
| Shirts | 4/1/2024 | 2024 Q3 | 287,189 | - | - | 40,000 | 33634 |
| Shirts | 5/1/2024 | 2024 Q3 | 327,189 | - | - | 40,000 | - |
| Shirts | 6/1/2024 | 2024 Q3 | 367,189 | - | - | 40,000 | - |
| Shirts | 7/1/2024 | 2024 Q4 | 407,189 | - | - | 40,000 | - |
| Shirts | 8/1/2024 | 2024 Q4 | 457,189 | - | - | 50,000 | - |
| Shirts | 9/1/2024 | 2024 Q4 | 497,189 | - | - | 40,000 | - |
| Shirts | 10/1/2024 | 2025 Q1 | 617,189 | - | - | 120,000 | - |
| Shirts | 11/1/2024 | 2025 Q1 | 657,189 | - | - | 40,000 | - |
| Shirts | 12/1/2024 | 2025 Q1 | 697,189 | - | - | 40,000 | - |
| Shirts | 1/1/2025 | 2025 Q2 | 787,089 | - | - | 89,900 | - |
| Shirts | 2/1/2025 | 2025 Q2 | 903,624 | - | - | 116,535 | - |
6 Replies
- HinnantjFrequent Visitor
Here is link to the sample data
- AnonymousNot 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 + ProductionRemaining_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
- HinnantjFrequent 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).
- HinnantjFrequent Visitor
Anonymous - Hi! Have you had a chance to review latest comments?