cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MahmoudElgendi
Frequent Visitor

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

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

MahmoudElgendi_0-1670218070473.png

 

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

1 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:

vyueyunzhmsft_0-1670310314608.png

 

 

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

View solution in original post

6 REPLIES 6
MahmoudElgendi
Frequent Visitor

Thanks @amitchandak 

 

I'm actually looking for a better solution

amitchandak
Super User
Super User

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

 

 

 

@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

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:

vyueyunzhmsft_0-1670310314608.png

 

 

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

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

MahmoudElgendi
Frequent Visitor

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors