previous year filter

Trying to calculate previous year results. based on filter selected from facts table months and years.

I am able to get the correct result by hardcoding the previous year such as 2015 but once I try ot use a measure it fails to calculate correctly.

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]=2015)))

Works with the exception thtat the result is always for 2015 regardless of which year I select in the filter.

I would like to select a year and get the previous years results, essentially year-1.

I have tryed:

[PY]=CALCULATE(MAX(GL_ACCOUNT_BALANCES1[CURRENT_YEAR]),ALLSELECTED(GL_ACCOUNT_BALANCES1))-1

Returns the correct value in my pivotable based on year selected. Example if I select 2014 it returns 2013.

I want to use this measure in a formula in place of hard coding 2015.

v-haibl-msft

10 years ago

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