Dynamic Trial Balance with Opening, Debit, Credit, Net Change and Ending Balance

[MahmoudElgendi]

Hello friends

I want to calculate the **Opening** **Balance** for the attached Trial Balance, the opening balance should be dynamic based on the selected date in the dates slicer.

Then the **Debit** and **Credit** is the Movement between the selected dates in the **dates slicer**
The Net Changes is simple = **Debit** - **Credit**

Finally, the **Ending Balance** is the sum of all the transactions until the selected slicer **max date**

Thanks in advance, I'm able to achieve the same in Power Query, but I like to create the same using DAX.

[AkshayGatkal]

Dear friends, Your solution works very well. But the Total balance is keep getting wrong. What would be possible reason. It is only taking one accoutns balances,

[MahmoudElgendi]

Thanks @amitchandak 

I'm actually looking for a better solution

[amitchandak]

@MahmoudElgendi , we have inventory method

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

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

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

or you can use opening balanace

Power Bi DAX Functions openingbalancemonth, openingbalancequarter, openingbalanceyear, firstnonblankvalue, and parallelperiod.Opening Stock, First value of period: https://youtu.be/6lzYOXI5wfo

BOP and EOP

Current Employees BOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]< Min(d_DateTable[Date]) &&
(ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>=Min('d_DateTable'[Date]))),
(f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

Current Employees EOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]<=max(d_DateTable[Date]) &&
(ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>max('d_DateTable'[Date]))),
(f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

[MahmoudElgendi]

@amitchandak 

My main issue is the Opening Balance, It should calc. like that, the Cumulative Sum of the Amount between the first date in the transactions until the Min Selected date in the Slicer

[v-yueyunzh-msft · Accepted solution]

Hi , @MahmoudElgendi 

I download your .pbix file and test in my side , do you mean you want to get the data from this No's minest date to the Min_selected_date. Right?

You can try to use this dax:

Opening Balance = 
 var _curno = MAX('ChartOfAccounts'[G/L Account No.])
 var _min_selected_date = [Min Selected Date]
 var _trans_date = MINX( FILTER( ALL('GL_Entries') , 'GL_Entries'[GLAccountNo] = _curno) , [Date])
 var _t = FILTER( ALL('GL_Entries') , 'GL_Entries'[GLAccountNo]=_curno && 'GL_Entries'[Date]< _min_selected_date && 'GL_Entries'[Date]>= _trans_date)
 return
SUMX(_t,[Amount])

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Hi Aniya,

I'm wondering if there is a way to see what each of your variables calculates to? I'm trying to understand what each one accomplishes.

[MahmoudElgendi]

@v-yueyunzh-msft  Thanks so much for your support

[MahmoudElgendi]

I tried to attach the file but I did not see the option to attach

I uploaded here on GoogleDrive https://drive.google.com/file/d/1zl0o9CohiAsL9CzXrL32qYNLaOV0Wr-B/view?usp=share_link