DAX: Calculate sum with filters over two tables

[Anonymous]

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

[amitchandak]

@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")

[Anonymous]

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!

[Anonymous]

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?!

[Mariusz · Accepted 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"
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn