Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello everyone, I need your support to solve and understand issues in my DAX. I have a transactionDB of all bank transaction like below:
date | company | type | Source | Bank | amount |
01-Apr-22 | Company 1 | Credit | Bank | Bank A | 10000 |
01-Apr-22 | Company 1 | Credit | Bank | Bank B | 10000 |
01-Apr-22 | Company 1 | Debit | Bank | Bank B | 500 |
01-Apr-22 | Company 2 | Credit | Bank | Bank C | 10000 |
01-Apr-22 | Company 2 | Credit | Bank | Bank D | 10000 |
01-Apr-22 | Company 2 | Debit | Bank | Bank C | 10000 |
02-Apr-22 | Company 1 | Debit | Bank | Bank A | 3000 |
02-Apr-22 | Company 1 | Debit | Bank | Bank B | 1000 |
03-Jun-22 | Company 2 | Credit | Bank | Bank D | 30000 |
03-Apr-22 | Company 1 | Credit | Bank | Bank A | 5000 |
03-Apr-22 | Company 2 | Credit | Bank | Bank C | 60000 |
03-Apr-22 | Company1 | Debit | Bank | Bank A | 2000 |
03-Apr-22 | Company 2 | Debit | Bank | Bank C | 10000 |
03-Apr-22 | Company 2 | Debit | Bank | Bank D |
I want to build a Dashboard with Matrix like below.
Users wants to see today (slider date 3/06/2022)
1. Opening balance of 03/06/2022 (sum of all debit - credit as of of 2/06/2022)
2. Sum of credit happened on 03/06/2022
3. Sum of debit happened on 03/06/2022
4. Closing balance of 03/06/2022
If the user changes the date slider to 2/06/202, they will see above status as of 02/06/2022
Solution
1. Opening Measure (This is working fine with slider and all values)
2. Credit (Matrix is showing wrong calculation)
3. Debit(Matrix is showing for wrong calculation)
I want to show the debit happened in Slider date ie. 03/06/2022. It is not working as expected.
4. Ending balance (This is working fine with slider and all values)
Hi @nizaeros ,
According to your description, I create a sample the same with yours.
But the Opening balance return nothing when select 2022/6/1-2022/6/3.
Most of your date are from April, I don't know why you select June in the slicer, if your sample data is not complete, please show the data related to the expected result. Then we can reproduce your problem.
Just from the formula, you can try to modify it like this:
Credit =
CALCULATE (
SUM ( transactionDB[credit] ),
FILTER (
ALLSELECTED ( transactionDB[date] ),
transactionDB[date]
= MAXX ( ALLSELECTED ( transactionDB[date] ), transactionDB[date] )
)
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@nizaeros , with help from a date table you need measure like
[Intial Balance] + CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
Closing Bal =
[Intial Balance] + CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
16 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |