What would be the DAX code for the discussed scenario?

[Muazam]

Hey there, I'm having trouble calculate correct closing balance. Need correct DAX for the psudocode that seems appropriate for the scenario depicted in the attached JPEG.

if No. of Transactions on Min (date) <=1 

   New Closing Balance = Closing Balance,

else

  find sum of all withdrawals on Min (date), find sum of all deposits on Min (date),

  if sum of all deposits on Min (date) >= sum of all withdrawals on Min (date)

     New Closing Balance = Max (Closing Balance) on Min (date)

  else

     New Closing Balance = Min (Closing Balance) on Min (date)

I hope the corresponding DAX code will help get the correct closing balance on Min (date).

Once we have new closing balance on Min (date), the new closing balance for subsequent dates would be as under:

   New closing Balance Today = New Closing Balance Yesterday + Sum of all deposits today - sum of all withdrawals today... 

[v-xinruzhu-msft]

Hi @Muazam 

You can refer to the following example.

You can create a new column in table

New Closing Balance = 
var _mindate=FILTER('Table',[Date]=MIN([Date]))
var _tilltoday=FILTER('Table',[Date]<=EARLIER([Date])&&[Date]<>MIN([Date]))
var _sumwith=SUMX(_mindate,[Withdrawal])
var _sumdepo=SUMX(_mindate,[Deposit])
var _if=IF(CALCULATE(DISTINCTCOUNT('Table'[Transactions]),'Table'[Date]=MIN([Date]))=1,[Closing Balance],IF(_sumdepo>=_sumwith,MAXX(_mindate,[Closing Balance]),MINX(_mindate,[Closing Balance])))
return _if+SUMX(_tilltoday,[Deposit])-SUMX(_tilltoday,[Withdrawal])

Best Regards,

Xinru Zhu

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

[amitchandak]

@Muazam , to me it very similar to inventory calculation

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

You can use Min for start of period

You might not need - CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date'))

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