Calculated measure using other calculated measure

[Nick2358]

Hello everyone,

I have this measure that calculates YTD Actual (-2 months) + Budget of months left.

When I use the following with "Calculate(SUM))" everything works perfectly:

Act + Bdgt =

var _max1 = MAXX(ALLSELECTED(Dates),Dates[Date_2])

var _max = EOMONTH(DATE(YEAR(_max1), MONTH(_max1)-2,1),0)

var _min = DATE(YEAR(_max1),1,1)

var _minb = DATE(YEAR(_max1), MONTH(_max1)-2,1)

var _maxb = DATE(YEAR(_max1), 12,31)

return

CALCULATE(SUM('Conso Bdgt 2020'[Value]), FILTER(Dates, Dates[Date_2] <=_max && Dates[Date_2] >=-_min))

+ CALCULATE(SUM('Calculation_Conso Bdgt 2020'[Value]), FILTER(Dates, Dates[Date_2]<=_maxb && Dates[Date_2]>=_minb))

Now when I use the same measure but with other calculated measures instead of "Calculate(SUM))" it doesn't work, underlines the last 2 lines in red and says on the first line "Unexpected expression 'Fillter'" and on the second "Cannot find name '[YTD_Bdgt_GP_Before_Com]], FILTER(Dates, Dates[Date_2]'".

It also says the syntax for ',' is incorrect.

I tried different things but nothing worked. 

Act + Bdgt_GP_Bef_Com =

var _max1 = MAXX(ALLSELECTED(Dates),Dates[Date_2])

var _max = EOMONTH(DATE(YEAR(_max1), MONTH(_max1)-2,1),0)

var _min = DATE(YEAR(_max1),1,1)

var _minb = DATE(YEAR(_max1), MONTH(_max1)-2,1)

var _maxb = DATE(YEAR(_max1), 12,31)

return

[YTD_GP_Before_Com], FILTER(Dates, Dates[Date_2] <=_max && Dates[Date_2] >=-_min)

+ [YTD_Bdgt_GP_Before_Com]], FILTER(Dates, Dates[Date_2]<=_maxb && Dates[Date_2]>=_minb

Thanks in advance for your help,

NIck

[DataInsights]

@Nick2358,

You need the CALCULATE function:

Act + Bdgt_GP_Bef_Com =
VAR _max1 =
    MAXX ( ALLSELECTED ( Dates ), Dates[Date_2] )
VAR _max =
    EOMONTH ( DATE ( YEAR ( _max1 ), MONTH ( _max1 ) - 2, 1 ), 0 )
VAR _min =
    DATE ( YEAR ( _max1 ), 1, 1 )
VAR _minb =
    DATE ( YEAR ( _max1 ), MONTH ( _max1 ) - 2, 1 )
VAR _maxb =
    DATE ( YEAR ( _max1 ), 12, 31 )
RETURN
    CALCULATE (
        [YTD_GP_Before_Com],
        FILTER ( Dates, Dates[Date_2] <= _max && Dates[Date_2] >= - _min ) + [YTD_Bdgt_GP_Before_Com]], FILTER(Dates, Dates[Date_2] <= _maxb
            && Dates[Date_2] >= _minb
    )

[Nick2358]

@DataInsights thanks a lot for your answer.

The function seems to work however it says now "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Any ideas what could cause this?

Thanks a lot for your helps,

Nick

[DataInsights · Accepted solution]

@Nick2358,

Try this. Each measure needs a CALCULATE. 

Act + Bdgt_GP_Bef_Com =
VAR _max1 =
    MAXX ( ALLSELECTED ( Dates ), Dates[Date_2] )
VAR _max =
    EOMONTH ( DATE ( YEAR ( _max1 ), MONTH ( _max1 ) - 2, 1 ), 0 )
VAR _min =
    DATE ( YEAR ( _max1 ), 1, 1 )
VAR _minb =
    DATE ( YEAR ( _max1 ), MONTH ( _max1 ) - 2, 1 )
VAR _maxb =
    DATE ( YEAR ( _max1 ), 12, 31 )
RETURN
    CALCULATE (
        [YTD_GP_Before_Com],
        FILTER ( Dates, Dates[Date_2] <= _max && Dates[Date_2] >= - _min )
    )
        + CALCULATE (
            [YTD_Bdgt_GP_Before_Com], FILTER(Dates, Dates[Date_2] <= _maxb
                && Dates[Date_2] >= _minb
        )

[Nick2358]

Hi @DataInsights ,

It worked,

Thanks a lot for your help,

Nick,