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

     

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 and it does not seem like var works for measures.

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

     

    • Drobinson1's avatar
      Drobinson1
      Helper III
      9 years ago

      We are on 2013 :(.

      Wish I could just upgrade to 2016.