Running Balance - Reset to Zero

[Hinnantj]

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	-

[Anonymous]

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]

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]

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

[Hinnantj]

Sample Data 

Here is link to the sample data

[Fowmy]

@Hinnantj 
Please provide access to open your file on Google Drive

[Hinnantj]

Access updated