cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Complex DAX suggestion

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

Solution Sage

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

Microsoft Employee

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors