How to include multiple years in SUMX function

[pbibmac_08]

Hey PBI Community - I am trying to write a query to sum up the life-to-date financials. This would mean filtering for several different fiscal years. I have tried with the query below but when I create this measure and drag it in to the PBI workspace nothing comes up. Any recommendations would be very much appreciated! 

LTD Actuals =

var FSG = SELECTEDVALUE('Trended PL'[FSG5])

var a =

    SUMX(

        FILTER('Trended PL',

        'Trended PL'[Scenario]="Actual"

         &&'Trended PL'[Fiscal Year]="FY2019"

         &&'Trended PL'[Fiscal Year]="FY2020"),

          'Trended PL'[PL Amount CYP])

return  IF(FSG="FSG10000",a,a*-1)

[Ahmedx]

I think you need this

LTD Actuals =
VAR FSG =
    SELECTEDVALUE ( 'Trended PL'[FSG5] )
VAR a =
    SUMX (
        FILTER (
            'Trended PL',
            'Trended PL'[Scenario] = "Actual"
                && 'Trended PL'[Fiscal Year] in {"FY2019","FY2020"}
        ),
        'Trended PL'[PL Amount CYP]
    )
RETURN
    IF ( FSG = "FSG10000", a, a * -1 )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please try something like below whether it suits your requirement.

LTD Actuals =
VAR FSG =
    SELECTEDVALUE ( 'Trended PL'[FSG5] )
VAR a =
    SUMX (
        FILTER (
            'Trended PL',
            'Trended PL'[Scenario] = "Actual"
                && OR (
                    'Trended PL'[Fiscal Year] = "FY2019",
                    'Trended PL'[Fiscal Year] = "FY2020"
                )
        ),
        'Trended PL'[PL Amount CYP]
    )
RETURN
    IF ( FSG = "FSG10000", a, a * -1 )