Running Balance - Reset to Zero

Hinnantj · ‎12-31-2023

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 · ‎12-31-2023

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 · ‎01-04-2024

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).