topic Re: Dynamic Trial Balance with Opening, Debit, Credit, Net Change and Ending Balance in DAX Commands and Tips

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

MahmoudElgendi — Mon, 05 Dec 2022 05:29:57 GMT

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.

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

MahmoudElgendi — Mon, 05 Dec 2022 05:31:58 GMT

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

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

amitchandak — Mon, 05 Dec 2022 06:01:00 GMT

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

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

MahmoudElgendi — Mon, 05 Dec 2022 19:26:41 GMT

Thanks amitchandak

I'm actually looking for a better solution

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

MahmoudElgendi — Tue, 06 Dec 2022 05:38:59 GMT

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

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

v-yueyunzh-msft — Tue, 06 Dec 2022 07:05:27 GMT

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

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

MahmoudElgendi — Tue, 06 Dec 2022 18:43:13 GMT

v-yueyunzh-msft Thanks so much for your support

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

Anonymous — Tue, 23 May 2023 18:17:27 GMT

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.

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

AkshayGatkal — Thu, 23 Nov 2023 21:33:04 GMT

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,