Forum Discussion

nizaeros's avatar
nizaeros
New Member
4 years ago

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:

 

datecompanytypeSourceBankamount
01-Apr-22Company 1CreditBankBank A10000
01-Apr-22Company 1CreditBankBank B10000
01-Apr-22Company 1DebitBankBank B500
01-Apr-22Company 2CreditBankBank C10000
01-Apr-22Company 2CreditBankBank D10000
01-Apr-22Company 2DebitBankBank C10000
02-Apr-22Company 1DebitBankBank A3000
02-Apr-22Company 1DebitBankBank B1000
03-Jun-22Company 2CreditBankBank D30000
03-Apr-22Company 1CreditBankBank A5000
03-Apr-22Company 2CreditBankBank C60000
03-Apr-22Company1DebitBankBank A2000
03-Apr-22Company 2DebitBankBank C10000
03-Apr-22Company 2DebitBankBank 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 
matrix calculation
Matrix grouped columns issue
MAX date

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.