Forum Discussion

Drobinson1's avatar
Drobinson1
Icon for Helper III rankHelper III
10 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
    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

     

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
10 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
    Icon for Helper III rankHelper III
    10 years ago

    Correct,

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

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    10 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
      Icon for Helper III rankHelper III
      10 years ago

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

    • Drobinson1's avatar
      Drobinson1
      Icon for Helper III rankHelper III
      10 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
        Icon for Microsoft Employee rankMicrosoft Employee
        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