Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 REPLY 1
ppm1
Solution Sage
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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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