Current day Ending balance should show up as Starting balance for next day

[Anonymous]

Hello Folks,

Need your help to figure out this interesting qusetion.

How to write a DAX expression/measure to show currrent day ending balance as a Starting balance for next day and continue addin up as shown in the example below:

Any ideas?

Thanks in advance. 

[amitchandak · Accepted solution]

@Anonymous , Agree with @ppm1 , You need something like

Starting Balanace = calculate(Sum(Table[Sale])- Sum(Table[Cost]), filter(all(Date), Date[Date] < Max(Date[Date]) ) )

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[marbi]

Have you solved this? I'm looking for the same DAX to be used

[v-jialluo-msft · Accepted solution]

^{Hi @Anonymous ,}

^{Please follow these steps:}

^{(1) Create a new measure}

Balance = 
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[Data] <= MAX ( 'Table'[Data] ) ),
    'Table'[Sales] - 'Table'[Cost]
)

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[amitchandak · Accepted solution]

@Anonymous , Agree with @ppm1 , You need something like

Starting Balanace = calculate(Sum(Table[Sale])- Sum(Table[Cost]), filter(all(Date), Date[Date] < Max(Date[Date]) ) )

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[ppm1]

You can't really do true recursion in DAX, but fortunately in your case it looks like you could do a running total of Sales and subtract the running total of costs (both up until the previous day).

Pat