Forum Discussion
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 ...
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
I am using powerpivot in excel and it does not seem like var works for measures.
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
We are on 2013 :(.
Wish I could just upgrade to 2016.