Complex DAX suggestion

Alicetemitope — Fri, 26 May 2023 09:56:02 GMT

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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

Re: Complex DAX suggestion

ppm1 — Fri, 26 May 2023 12:17:16 GMT

Unfortunately, you've inherited some bad DAX. Rather than try to explain/update this measure, I suggest you explain your model, provide sample/mock data (not as an image) if possible, and explain the calculation you need. You will get there faster and have more performant and simpler DAX to maintain going forward.

Pat

topic Re: Complex DAX suggestion in DAX Commands and Tips

Complex DAX suggestion

Re: Complex DAX suggestion