topic Re: Matrix Total is not correct in DAX Commands and Tips

Matrix Total is not correct

bedata1 — Tue, 11 Apr 2023 06:26:13 GMT

Hi Community,

Situation

I created a Matrix visual with two tables "GeneralLedgerEntries" (Actual Amount) & "Budget" (Budget Amount).

I created the follow DAX (with Date = 2023,01,31 --> this means it should show the actual amount up to that date, from then on the budget amount):

LE = var SelectedDate = Date(2023,01,31) var ActualAmount = CALCULATE( SUM(GeneralLedgerEntries[Amount]), FILTER(GeneralLedgerEntries, GeneralLedgerEntries[Posting_Date] <= SelectedDate) ) var BudgetAmount = CALCULATE( SUM(Budget[Amount]), FILTER(Budget, Budget[Date] > SelectedDate) ) return IF( MAX(Budget[Date]) > SelectedDate, BudgetAmount, ActualAmount )

Problem

I would like to show the total per quarter, but unfortunately the total is not correct.
It only adds the two columns "2023.02"+"2023.03" which come from the table "Budget". However, "2023.01"+"2023.02"+"2023.03" should calculate ("2023.01" come from the table "GeneralLedgerEntries")...

Does anyone have an idea what it could be so I can calculate the correct total?

Re: Matrix Total is not correct

FreemanZ — Tue, 11 Apr 2023 08:23:57 GMT

hi bedata1

try like:

LE =

var SelectedDate = Date(2023,01,31)

var ActualAmount =

CALCULATE(

SUM(GeneralLedgerEntries[Amount]),

FILTER(GeneralLedgerEntries, GeneralLedgerEntries[Posting_Date] <= SelectedDate)

)

var BudgetAmount =

CALCULATE(

SUM(Budget[Amount]),

FILTER(Budget, Budget[Date] > SelectedDate)

)

return

BudgetAmount + ActualAmount

Re: Matrix Total is not correct

tamerj1 — Tue, 11 Apr 2023 08:31:35 GMT

Hi bedata1
Please try

LE = SUMX ( VALUES ( Budget[YearMonth] ), VAR SelectedDate = DATE ( 2023, 01, 31 ) VAR ActualAmount = CALCULATE ( SUM ( GeneralLedgerEntries[Amount] ), FILTER ( GeneralLedgerEntries, GeneralLedgerEntries[Posting_Date] <= SelectedDate ) ) VAR BudgetAmount = CALCULATE ( SUM ( Budget[Amount] ), FILTER ( Budget, Budget[Date] > SelectedDate ) ) RETURN IF ( CALCULATE ( MAX ( Budget[Date] ) ) > SelectedDate, BudgetAmount, ActualAmount ) )

Re: Matrix Total is not correct

bedata1 — Tue, 11 Apr 2023 19:56:58 GMT

FreemanZ Thank you, it works - as always, a perfect and fast help!
tamerj1 Thank you as well for your help.