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 crea...
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 )
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 )