cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Matrix Total is not correct

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?

1 ACCEPTED SOLUTION
Super User

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
3 REPLIES 3
Frequent Visitor

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

Super User

Hi @bedata1

``````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
)
)``````
Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors