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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nizaeros
New Member

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.

nizaeros_0-1656403719392.png

 

 

nizaeros_0-1656403291162.png

 

 


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 
2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1657024669498.png

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.

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors