Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am looking for a solution for my following problem. I have a data model with multiple tables and one of them is a Date-Table. The other table consists of some transactions. This is related to eachother on date-to-date, one-direction from Date-to-Transations table. In the whole report, I have a report filter on the year in the Date-table. So for example 2019 is selected.
I want to calculate the sum over the amount in the Transactions-table for all the transactions before and in 2019, but only for a specific AccountCode in the Transactions-table. See te Measure below.
Measure = CALCULATE (sum('rFinance V_Reportingbalance'[Amount])*-1
; FILTER (ALL('rAlgemeen V_Datum')
; YEAR('rAlgemeen V_Datum'[datum]) <= YEAR(MAX('rAlgemeen V_Datum'[datum])
)
; FILTER (ALL('rFinance V_Reportingbalance')
;'rFinance V_Reportingbalance'[GLAccountCode] = "XXX"
)
)
Is there anyone who can shed a light on this and make me see the light :-)?
Thanks,
Ivar
Solved! Go to Solution.
Hi @Anonymous
Use variable like.
Measure =
VAR __year = YEAR( MAX('rAlgemeen V_Datum'[datum] ) )
RETURN
CALCULATE (
SUM( 'rFinance V_Reportingbalance'[Amount] ) *-1;
FILTER (
ALL( 'rAlgemeen V_Datum' );
YEAR( 'rAlgemeen V_Datum'[datum] ) <= __year
);
FILTER(
ALL('rFinance V_Reportingbalance');
'rFinance V_Reportingbalance'[GLAccountCode] = "XXX"
)
)
@Anonymous What is the issue you are getting. Are you getting null or more value . Formula seem fine. Can Try like
Measure =
var _maxx= YEAR(MAXx('rAlgemeen V_Datum','rAlgemeen V_Datum'[datum]))
return
CALCULATE (sum('rFinance V_Reportingbalance'[Amount])*-1
; FILTER (ALL('rAlgemeen V_Datum') ; YEAR('rAlgemeen V_Datum'[datum]) <=_maxx)
; 'rFinance V_Reportingbalance'[GLAccountCode] = "XXX")
Depending what I was trying, I got no records at all, or all the records without the date-filter.
But the solution posted by Mariusz worked! Awesome, thanks a lot!
Hi all,
The variable works awesome for the measure! But I want to apply this also to a CALCULATETABLE(). In the measure, the __year value is always the selected value of the year on the page.
This works:
measure =
VAR __year = YEAR( MAX('rAlgemeen V_Datum'[datum] ) )
RETURN
__year
But when I use the code below, I always get the sales from 2030, the max year in the date-table. If i replace the variable __year with a hardcoded value, it works the way I want.
table =
VAR __year = YEAR( MAX('rAlgemeen V_Datum'[datum] ) )
RETURN
CALCULATETABLE('General Sales'; FILTER(ALL('General Sales'); 'General Sales'[Year] = __year))
Strange huh?!
Hi @Anonymous
Use variable like.
Measure =
VAR __year = YEAR( MAX('rAlgemeen V_Datum'[datum] ) )
RETURN
CALCULATE (
SUM( 'rFinance V_Reportingbalance'[Amount] ) *-1;
FILTER (
ALL( 'rAlgemeen V_Datum' );
YEAR( 'rAlgemeen V_Datum'[datum] ) <= __year
);
FILTER(
ALL('rFinance V_Reportingbalance');
'rFinance V_Reportingbalance'[GLAccountCode] = "XXX"
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |