Max function showing wrong. Need help for Powerbi Matrix total
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) CALCULATE( SUM(transactionDB[Amount]), FILTER(ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date],MAX(transactionDB[date])-1,DESC))) 2. Credit (Matrix is showing wrong calculation) CALCULATE( SUM(transactionDB[credit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) I want to show the credit happened in Slider date ie. 03/06/2022. It is not working as expected. 3. Debit(Matrix is showing for wrong calculation) CALCULATE( SUM(transactionDB[debit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) 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) CALCULATE( SUM(transactionDB[Amount]), FILTER( ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date], MAX(transactionDB[date]), DESC) ) ) Please help to solve this issue. I understand that using max function will summarize the value based on maximum date filtered within its context. But i would like to show the debit or credit happened for that particular date as per date slider ChandeepChhabra GuyInACube johnt75 tamerj1
