Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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 , 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))
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:
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |