Hey Guys,
I hope you are all doing great. So I started at a new job and inherited this report from a past PBI analyst. I am really confused about this formula and I was wondering if anyone can help explain it or provide a better way of writing it if you are up for the challenge. I am new to PBI myself and I am lost.
Thanks
Accumulated Projection (6-Month Average) =
VAR _Last6MonthsAverageConsumption = [Average Consumption of Last 6 Months]
VAR _Last6MonthsAverageConsumptionGrowth = [Avg Amortized Consumption Growth Rate of Last 6 Months] --Values CY
VAR _JanuaryCurrentYearValue = [Average Consumption of Last 6 Months] * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _FebruaryCurrentYearValue = _JanuaryCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _MarchCurrentYearValue = _FebruaryCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _AprilCurrentYearValue = _MarchCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _MayCurrentYearValue = _AprilCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _JuneCurrentYearValue = _MayCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _JulyCurrentYearValue = _JuneCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _AugustCurrentYearValue = _JulyCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _SeptemberCurrentYearValue = _AugustCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _OctoberCurrentYearValue = _SeptemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _NovemberCurrentYearValue = _OctoberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _DecemberCurrentYearValue = _NovemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) --Values NY
VAR _JanuaryNextYearValue = _DecemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _FebruaryNextYearValue = _JanuaryNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _MarchNextYearValue = _FebruaryNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _AprilNextYearValue = _MarchNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _MayNextYearValue = _AprilNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _JuneNextYearValue = _MayNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _JulyNextYearValue = _JuneNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _AugustNextYearValue = _JulyNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _SeptemberNextYearValue = _AugustNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _OctoberNextYearValue = _SeptemberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _NovemberNextYearValue = _OctoberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
VAR _DecemberNextYearValue = _NovemberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] )
RETURN
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 1,
_JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 2,
_FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 3,
_MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 4,
_AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 5,
_MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 6,
_JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 7,
_JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 8,
_AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 9,
_SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 10,
_OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 11,
_NovemberCurrentYearValue + _OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( DIM_Date[Month] ) = 12,
_DecemberCurrentYearValue + _NovemberCurrentYearValue + _OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 1,
_JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 2,
_FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 3,
_MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 4,
_AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 5,
_MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 6,
_JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 7,
_JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 8,
_AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 9,
_SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 10,
_OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 11,
_NovemberNextYearValue + _OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue,
IF (
SELECTEDVALUE ( DIM_Date[Year] )
= YEAR ( TODAY () ) + 1
&& SELECTEDVALUE ( DIM_Date[Month] ) = 12,
_DecemberNextYearValue + _NovemberNextYearValue + _OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
