Measure: Combine two calculations into one

[NatK]

Hi,

I have the following two working calculations in my measure that I would like to combine into one calculation using an OR (||) statement: 

balanceA calculates Amount when the date equals the last day of the selected period and Closing Entry = 0.

VAR balanceA = CALCULATE(SUM(testdata[Amount]), FILTER(testdata, testdata[Closing Entry]=0 && RELATED(vw_bi_date[TheDate])=lastDayofSelectedPeriod)) 

balanceB calculates Amoung when the date is less than the last day of the selected period. 

VAR balanceB = CALCULATE(SUM(testdata[Amount]), ALL(vw_bi_date[TheDate]), vw_bi_date[TheDate] < lastDayofSelectedPeriod) 

I tried to combine balanceA and balanceB with balanceC below, but I am getting an error that 'A function 'FILTER' has been used  in a True/False expression that is used as a table filter expression. This is not allowed.' 

VAR balanceC =
CALCULATE(SUM(testdata[Amount]),
FILTER(testdata, testdata[Closing Entry]=0 && RELATED(vw_bi_date[TheDate])=lastDayofSelectedPeriod) || ALL(vw_bi_date[TheDate]), vw_bi_date[TheDate] < lastDayofSelectedPeriod)

For additional context, I have a 'month & year' slicer, but the final calculation ultimately needs to capture cumulative data through the last date of the selected month.

Any thoughts on how to combine balanceA and balanceB successfully? Thanks in advance!

[Anonymous]

HI @NatK,

I do not so recommend you combine them into one calculation function with multiple conditions. (By default, Dax calculate function used the 'AND' logic to link all filter conditions)

For your scenario, I'd like to suggest you nested calculate functions and move the public part outer and another part internal.

measure =
CALCULATE (
    CALCULATE ( SUM ( Table[Amount] ), FILTER ( T1, 'condition' ) ) //calculate 1
        + CALCULATE ( SUM ( Table[Amount] ), FILTER ( T2, 'condition 2' ) ), //calculate 2
    FILTER ( T3, 'condition 3' ) //public filter
)

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@NatK  When you say 'combine balanceA and balanceB' do you mean SUM? You could either simply add the variables or try: 

VAR balanceC =

CALCULATE(

SUM(testdata[Amount]),

FILTER(testdata,

(testdata[Closing Entry]=0 && RELATED(vw_bi_date[TheDate])=lastDayofSelectedPeriod)

||

RELATED(vw_bi_date[TheDate])<lastDayofSelectedPeriod

)

)