Forum Discussion

Drobinson1's avatar
Drobinson1
Helper III
9 years ago
Solved

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:

 

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.

  • v-haibl-msft's avatar
    v-haibl-msft
    9 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

     

7 Replies

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    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?

     

     

    • Drobinson1's avatar
      Drobinson1
      Helper III
      9 years ago

      Correct,

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

    • CahabaData's avatar
      CahabaData
      Memorable Member
      9 years ago

      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's avatar
        Drobinson1
        Helper III
        9 years ago

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