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!