Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

8 REPLIES 8
AkshayGatkal
Regular Visitor

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,Capture.JPG

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

Anonymous
Not applicable

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.

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.