previous year filter

[Drobinson1]

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:

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

[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.

[CahabaData]

So if I understand; the top statement works - with 2015 manually entered into the statement.

The bottom statement is the same but you want to use a variable [PY]

Did you declare [PY] as a variable?

[CahabaData]

I would try opening your measure the variable declaration:

YourMeasure = VAR

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

                          RETURN

                          CALCULATE ( ....your 2nd statement.....

[Drobinson1]

I am using powerpivot in excel and it does not seem like var works for measures.

[v-haibl-msft · Accepted solution]

@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

[Drobinson1]

We are on 2013 :(.

Wish I could just upgrade to 2016.

[Drobinson1]

I am using powerpivot in excel.  VAR does not seem to be an option?

[Drobinson1]

Correct,

Didnt seem like I was able to decalre a variable since it was a measure?