Use previous row value to calculate next date sales

[kmax]

Hello, 

I need to get previous row ending balance as opening balance of next row.

OpeningBalance
For 1st day, the initial amount is coming from another table. I have a measure for each year that fetches the value based on year selected.
For the rest of the dates, the value of EndingBalance of previous date is the opening balance.

Sales
This column contains sales amount.

EndingBalance
MonthlyEndingBalance = MonthlyOpeningBalance + MonthSales

Example.
For 1st day
OpeningBalance=10

Sales=15

EndingBalance  (OpeningBalance+Sales)  =25 

For next day

OpeningBalance=25 (EndingBalance of previous row)

Sales=20

EndingBalance  (EndingBalance of previous row +Sales)  = 45

repeat same logic until the last availabe date. 

Thanks in advance

[kmax]

Hello, thanks for the details. I think i missed some details. 
Here is the PBIX file with dummy data. Logic is same but it tells the whole story if you can take a look at it.
Download PBIX here 
I have two table visuals in this file. Both with different logic as below.
I am facing issue in getting the correct sum using these logics.

Option 1 - using calculated column

The ending Balance column is working fine till 4th month.
From 5th month, the ending balance is showing wrong value.
2024-03
Ending Balance column = 1893980
2024-04
Month sale column = -65471

2024-04 Ending balance =

1893980-65471=1828509 (correct value)
but it is showing 2055416.
if we subtract
1828509-2055416 = -226907 (this is month sale value of 2024-01.
same pattern continues for rest of the months of any year.

Option 2 - using measures
from 2nd month onward, the Opening Balance adjusted value is correctly fetching from closing balance measure value of previous month.
but the closing balance measure is showing wrong answer for all months.
it is doing
opening balance + monthly variation = closing balance measure
this is fine for 1st month. From 2nd month, it should set the opening balance value from closing balance of previous month but it is using the 1st month value of opening balance for all months.

[shafiz_p · Accepted solution]

@kmaxI would suggest you go through your table, and do manual filtering on table to check the result (all filters you applied on the visuals). Let's see an example,

Option 1 :

Your initial filter was, Month Number, Year, LoB.

Measure you have: Month Sale Column = SELECTEDVALUE(Activitylist[VariationEndingCARRColumn]), this measure doing no calculation but to selecting value from the Activitylist table. Let's see what was your value from year = 2024, LoB = HR, MonthNo = 4. I have found -65471.

Now you need ending balance of previous month. Your ending balance formula is, Ending Balance Column = SELECTEDVALUE(Activitylist[EndingCARRColumn]), which again doing no calculation
but to selecting values of current month which is 4 from the Activitylist[EndingCARRColumn]. Which is 2055416. and no further calculation specified to do. That's why you are seeing this value instead of 1828509.

Now, some how you need to tell your measure that go to the table and findout value of previous month
from "EndingCARRColumn" and then add [Month Sale Column]

Remember, measure calculates on the fly against table and column we specify. So, I think you need to fix your "EndingCARRColumn" code to see the correct result, if you think it wrong.

You can use the below code to add current month sales with the previous month ending balance:

Ending Balance Column =

VAR _selectedMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])

VAR _PrevMonth = _selectedMonth - 1

VAR _endingBalPrevMonth = CALCULATE(MAX(Activitylist[EndingCARRColumn]), Activitylist[FX_MonthNumber] = _PrevMonth)

RETURN

IF(

    _selectedMonth = 1,

    MAX(Activitylist[EndingCARRColumn]),

_endingBalPrevMonth + [Month Sale Column]

)

Hope you found this useful.

[shafiz_p]

@kmax Use the below dax code to find out previous row value :

PrevRow =

VAR _PreviousDate = MAX('Table'[Date]) - 1

VAR _Result =

CALCULATE(

    MAX('Table'[EndingBalance]),

    'Table'[Date]=_PreviousDate

)

RETURN

_Result

If you want to add a column of opening balance from ending balance, then you can use power query to add Previous row value using self join. You will need 2 index column from 1 and from 0 to acheive this. 

Hope this helps!

If this solved you problem, please mark it as a solution!!