Forum Discussion

pbibmac_08's avatar
pbibmac_08
New Member
3 years ago
Solved

How to include multiple years in SUMX function

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)

Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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 )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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 )
  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    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 )