Forum Discussion
Hinnantj
2 years agoFrequent Visitor
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 di...
Anonymous
2 years agoNot 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
- Hinnantj2 years agoFrequent 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).
- Hinnantj2 years agoFrequent Visitor
Anonymous - Hi! Have you had a chance to review latest comments?