New Enterprise DNA Initiatives

Finding Max and Min of Variables in a Measure

DAX Calculations
#1

Hello–I am fairly new to Power BI and am excited about learning more. The DNA Enterprise website has been very helpful to my knowledge development. I work for a company that assists local governments.

I am trying to find how to calculate a forecast of future tax revenues by using the average of 5 years of taxes. I have a measure started that I obtained from one of Sam’s videos on forecasting. What I have so far is below.

Tax Forecast =
VAR
TaxesLY = CALCULATE( [Total Taxes], DATEADD( dDate[Date], -1, YEAR))
VAR
Taxes2YrsAgo = CALCULATE( [Total Taxes], DATEADD( dDate[Date], -2, YEAR))
VAR
Taxes3YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -3, YEAR))
VAR
Taxes4YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -4, YEAR))
VAR
Taxes5YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -5, YEAR))

Return
DIVIDE(TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo, 5, 0 )

However–what I am wanting to do is to forecast the average with the highest and lowest years eliminated from the calculation. I have been unable to find how to calculate the MAX of the 5 years, MIN of the 5 years, and then eliminate those figures from the calcualation.

Thank you all in advance and stay safe!

#2

@tjohnson,

You’re off to a good start here. To get the Min and Max, and then the average w/ min and max removed you can do this:

VAR MaxLast5 = MAX( TaxesLY, Taxes2YrsAgo, Taxes3YrsAgo, Taxes4YrsAgo, Taxes5YrsAgo)
VAR MinLast5 = MIN( TaxesLY, Taxes2YrsAgo, Taxes3YrsAgo, Taxes4YrsAgo, Taxes5YrsAgo)

VAR Total5 = TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo

VAR Middle3 = Total5 - MinLast5 - MaxLast5
VAR AvgMiddle3 = DIVIDE( Middle3, 3, 0)

I hope this is helpful.

  • Brian
#3

Thank you so much, Brian! I will try this out in a little while. I am currently in an online meeting. I appreciate your help!

#4

@tjohnson,

Sure – glad to help! If it works when you try it, please mark as solution, or if there are still issues we need to resolve, just give a shout.

  • Brian
#5

Brian–I am receiving an error after the measure has been validated-

Tax Forecast =
VAR
TaxesLY = CALCULATE( [Total Taxes], DATEADD( dDate[Date], -1, YEAR))
VAR
Taxes2YrsAgo = CALCULATE( [Total Taxes], DATEADD( dDate[Date], -2, YEAR))
VAR
Taxes3YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -3, YEAR))
VAR
Taxes4YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -4, YEAR))
VAR
Taxes5YrsAgo = CALCULATE( [Total Taxes], DateAdd (dDate[Date], -5, YEAR))

VAR MaxLast5 = Max(TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo)
VAR MinLast5 = Min(TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo)

VAR Total5 = TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo

Var Middle3 = Total5 - MinLast5 - MaxLast5
Var AvgMiddle3 = DIVIDE(Middle3, 3, 0)

#6

@tjohnson,

When you use variables, you always need to close with a RETURN statement. Try adding

RETURN
AvgMiddle3

to the end of your measure .

  • Brian
#7

Brian

I added the line as you suggested. Now I am receiving the following error.

The MAX function only accepts a column reference as an argument.

Any suggestsion?

Thanks!

#8

@tjohnson,

Can you please post your PBiX? Should be easy to sort out from there.

  • Brian
#9

It is a huge file… Any other way?

#10

@tjohnson,

Yup. Never mind - I see the problem. Will have revised code to you shortly…

  • Brian
#11

When I had the measure it was originally written, it worked. I just needed to exclude the Min and Max of the variables.

When I looked up the Max function, it states

MIN(, )

So I assume it will only take 2 variables?

#12

Sorry–it cut off my copy/paste

Max(,)

Min(,expression1>,)

#13

@tjohnson,

Correct. Setting it up as a virtual table now so we can take the MAXX/MINX of that virtual table column.

  • Brian
#14

@tjohnson,

Sorry for the delay - was needlessly overcomplicating things. Just needed to nest the MIN and MAX functions to get around the two parameter limit. Try this:

Tax Forecast2 =

VAR TaxesLY = CALCULATE( [Total Taxes], DATEADD( Dates[date], -1, YEAR))
VAR Taxes2YrsAgo = CALCULATE( [Total Taxes], DATEADD( Dates[date], -2, YEAR))
VAR Taxes3YrsAgo = CALCULATE( [Total Taxes], DATEADD( Dates[date], -3, YEAR))
VAR Taxes4YrsAgo = CALCULATE( [Total Taxes], DATEADD( Dates[date], -4, YEAR))
VAR Taxes5YrsAgo = CALCULATE( [Total Taxes], DATEADD( Dates[date], -5, YEAR))

VAR MaxLast5 = MAX(TaxesLY, MAX(Taxes2YrsAgo, MAX(Taxes3YrsAgo, MAX(Taxes4YrsAgo, Taxes5YrsAgo))))
VAR MinLast5 = MIN(TaxesLY, MIN(Taxes2YrsAgo, MIN(Taxes3YrsAgo, MIN(Taxes4YrsAgo, Taxes5YrsAgo))))

VAR Total5 = TaxesLY + Taxes2YrsAgo + Taxes3YrsAgo + Taxes4YrsAgo + Taxes5YrsAgo
VAR Middle3 = Total5 - MinLast5 - MaxLast5
VAR AvgMiddle3 = DIVIDE(Middle3, 3, 0)

RETURN
AvgMiddle3

I hope this is helpful.

1 Like
#15

I’d like to offer an alternative to this construct. Because you can create a single column called “Value” using list operators { } combine that with an iterator and you get:

VAR MaxLast5 = MAXX( {TaxesLY, Taxes2YrsAgo, Taxes3YrsAgo, Taxes4YrsAgo, Taxes5YrsAgo}, [Value] ) 
VAR MinLast5 = MINX( {TaxesLY, Taxes2YrsAgo, Taxes3YrsAgo, Taxes4YrsAgo, Taxes5YrsAgo}, [Value] )

I hope this is helpful.

2 Likes
#16

@Melissa,

Ha! - thanks! That was actually the first thing I tried, but I tried it w/ MAX, not MAXX. That’s definitely cleaner than nesting 4 MAX statements. :grinning:

  • Brian
#17

Thank you both, @Melissa and @Brianj! This works great!! Can I mark both of you as having solved the problem? New to the forum. Thank you!

#18

@tjohnson,

Great - glad that got you what you needed. Sorry for the circuitous route to get there - I chalk it up to “Friday Afternoon Brain” … :grinning:

  • Brian