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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
bedata1
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")...

bedata1_0-1681194345970.png

 

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

1 ACCEPTED SOLUTION
FreemanZ
Super User
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

View solution in original post

3 REPLIES 3
bedata1
Frequent Visitor

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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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