previous year filter

Drobinson1

According to this document, VAR Function (DAX) is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop.

You should be able to use following measure formula with Power Pivot in Excel 2016.

PY Balance =
VAR PY =
    CALCULATE (
        MAX ( GL_ACCOUNT_BALANCES1[CURRENT_YEAR] ),
        ALLSELECTED ( GL_ACCOUNT_BALANCES1 )
    )
        - 1
RETURN
    (
        CALCULATE (
            SUM ( GL_ACCOUNT_BALANCES1[Value] ),
            FILTER (
                ALL ( GL_ACCOUNT_BALANCES1[ACCOUNT_IDENT] ),
                GL_ACCOUNT_BALANCES1[ACCOUNT_IDENT] = "Actual"
            ),
            FILTER (
                ALL ( GL_ACCOUNT_BALANCES1[CURRENT_YEAR] ),
                GL_ACCOUNT_BALANCES1[CURRENT_YEAR] = PY
            )
        )
    )

Best Regards,

Herbert