Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Please suggest me a set expression for variance calculation as per the data highlighted below. 1st data refers to variance of expenses between Months whereas 2nd one refers to variance of income between two Years!
Regds
Neville
EXPENSES | |||||
MONTH | ACT | BUDGET | VARIANCE (ACT Vs BUDGET) | ||
JAN | 5000 | 3000 | 2000 | ||
FEB | 4000 | 5000 | -1000 | ||
MAR | 3000 | 3000 | 0 | ||
INCOME | |||||
MONTH | ACT-CY | ACY-LY | VARIANCE (CY Vs LY) | ||
JAN | 7500 | 10000 | -2500 | ||
FEB | 10000 | 11000 | -1000 | ||
MAR | 5000 | 4000 | 1000 | ||
What is you want to do specifically Neville? Could you explain a little more?
Compare two years variance with current year?
Felipe.
Dear Felip,
1st the difference between ACT to Budget, 2nd , CY vs Ly after that the difference created against the LY in both cases separately.
Rgds
Neville
for Expense
1st Expression (ACT)
(SUM({$<FinancialYear=,MonthName=,Quarter=,FiscalYear={$(=max(FiscalYear))}
,[Date]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Date]))"}
>}[Actual])
2nd Expression(Budget)
(SUM({$<FinancialYear=,MonthName=,Quarter=,FiscalYear={$(=max(FiscalYear))}
,[Date]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Date]))"}
>}Budget)
)
3rd Expression (variance)
Column(1)-Column(2)