Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to Solution.
hi @bedata1
try like:
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
)
)
hi @bedata1
try like:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
23 | |
12 | |
11 | |
10 |