How to write a Measure that will sum up previous 5 year of data?

[dc7669]

Hi, 

After some manipulation, I got the below table under "Transform" in PowerBI.

FiscalYear	Area	COUNTY	Category	Counts
2011	Region 5	Atkinson	Land Clearing	279
2010	Region 5	Atkinson	Agriculture	78
2018	Region 2	Baldwin	Land Clearing	359
2013	Region 5	Atkinson	Agriculture	74
2013	Region 2	Baldwin	Silviculture	60
2020	Region 2	Baldwin	Silviculture	27
2011	Region 2	Baldwin	Silviculture	53
2018	Region 2	Baldwin	Agriculture	43
2011	Region 5	Atkinson	Agriculture	118
2009	Region 5	Atkinson	Land Clearing	101
2014	Region 5	Atkinson	Agriculture	73
2014	Region 5	Atkinson	Land Clearing	302
2022	Region 5	Atkinson	Silviculture	7
2017	Region 2	Baldwin	Land Clearing	330
2012	Region 5	Atkinson	Land Clearing	586
2020	Region 2	Baldwin	Agriculture	10
2017	Region 5	Atkinson	Silviculture	233
2009	Region 2	Baldwin	Agriculture	39
2005	Region 2	Baldwin	Land Clearing	394
2012	Region 5	Atkinson	Agriculture	88
2010	Region 5	Atkinson	Land Clearing	381
2015	Region 5	Atkinson	Land Clearing	460
2013	Region 5	Appling	Land Clearing	1210
2007	Region 5	Appling	Silviculture	1
2013	Region 5	Atkinson	Silviculture	193
2022	Region 5	Appling	Land Clearing	9
2020	Region 5	Atkinson	Land Clearing	157
2015	Region 5	Appling	Silviculture	400
2012	Region 5	Appling	Agriculture	232
2010	Region 5	Appling	Agriculture	214
2008	Region 2	Baldwin	Agriculture	16
2011	Region 2	Baldwin	Agriculture	87
2017	Region 5	Appling	Agriculture	155
2015	Region 2	Baldwin	Agriculture	67
2014	Region 2	Baldwin	Silviculture	47
2008	Region 2	Baldwin	Silviculture	53
2021	Region 5	Atkinson	Land Clearing	140
2017	Region 5	Appling	Land Clearing	931
2020	Region 5	Appling	Silviculture	330
2016	Region 5	Appling	Land Clearing	1037
2019	Region 2	Baldwin	Land Clearing	275
2023	Region 5	Appling	Silviculture	1
2011	Region 5	Appling	Silviculture	513
2020	Region 5	Atkinson	Silviculture	237
2019	Region 5	Atkinson	Silviculture	111
2015	Region 5	Atkinson	Silviculture	156
2018	Region 5	Appling	Agriculture	224
2012	Region 5	Appling	Silviculture	587
2018	Region 5	Appling	Land Clearing	856
2016	Region 5	Atkinson	Land Clearing	401
2021	Region 5	Appling	Land Clearing	820
2016	Region 2	Baldwin	Land Clearing	236
2012	Region 2	Baldwin	Silviculture	30
2016	Region 5	Appling	Agriculture	174
2024	Region 5	Appling	Land Clearing	3
2016	Region 2	Baldwin	Agriculture	53
2019	Region 5	Atkinson	Land Clearing	209
2012	Region 5	Appling	Land Clearing	1064
2014	Region 2	Baldwin	Land Clearing	405
2018	Region 5	Appling	Silviculture	258
2010	Region 5	Atkinson	Silviculture	138
2017	Region 2	Baldwin	Agriculture	52
2005	Region 5	Appling	Land Clearing	3
2015	Region 5	Atkinson	Agriculture	62
2011	Region 5	Appling	Agriculture	373
2020	Region 5	Atkinson	Agriculture	50
2015	Region 2	Baldwin	Silviculture	58
2013	Region 5	Atkinson	Land Clearing	473
2011	Region 5	Appling	Land Clearing	641
2019	Region 2	Baldwin	Agriculture	35
2017	Region 5	Appling	Silviculture	228
2018	Region 5	Atkinson	Land Clearing	293
2006	Region 2	Baldwin	Agriculture	5
2015	Region 5	Appling	Land Clearing	1026
2019	Region 2	Baldwin	Silviculture	72
2006	Region 2	Baldwin	Silviculture	6
2014	Region 2	Baldwin	Agriculture	69
2005	Region 2	Baldwin	Silviculture	7
2014	Region 5	Atkinson	Silviculture	171
2017	Region 5	Atkinson	Agriculture	85
2012	Region 5	Atkinson	Silviculture	92
2017	Region 2	Baldwin	Silviculture	50
2017	Region 5	Atkinson	Land Clearing	352
2019	Region 5	Appling	Agriculture	124
2019	Region 5	Appling	Land Clearing	739
2022	Region 2	Baldwin	Land Clearing	4
2013	Region 2	Baldwin	Land Clearing	380
2011	Region 5	Atkinson	Silviculture	207
2018	Region 2	Baldwin	Silviculture	61
2024	Region 5	Atkinson	Land Clearing	1
2016	Region 2	Baldwin	Silviculture	62
2011	Region 2	Baldwin	Land Clearing	228
2013	Region 2	Baldwin	Agriculture	57
2024	Region 2	Baldwin	Land Clearing	3
2015	Region 2	Baldwin	Land Clearing	240
2014	Region 5	Appling	Silviculture	392
2021	Region 5	Appling	Agriculture	155
2019	Region 5	Atkinson	Agriculture	54
2022	Region 5	Atkinson	Agriculture	2
2006	Region 2	Baldwin	Land Clearing	566
2023	Region 5	Atkinson	Silviculture	1
2020	Region 5	Appling	Land Clearing	956
2015	Region 5	Appling	Agriculture	178
2020	Region 2	Baldwin	Land Clearing	287
2018	Region 5	Atkinson	Agriculture	64
2009	Region 5	Atkinson	Silviculture	15
2021	Region 5	Atkinson	Silviculture	279
2007	Region 2	Baldwin	Agriculture	3
2016	Region 5	Atkinson	Silviculture	162
2012	Region 2	Baldwin	Land Clearing	246
2021	Region 5	Appling	Silviculture	249
2008	Region 2	Baldwin	Land Clearing	1249
2014	Region 5	Appling	Agriculture	241
2020	Region 5	Appling	Agriculture	81
2010	Region 2	Baldwin	Silviculture	77
2021	Region 2	Baldwin	Land Clearing	382
2010	Region 5	Appling	Silviculture	256
2009	Region 2	Baldwin	Silviculture	69
2016	Region 5	Appling	Silviculture	359
2016	Region 5	Atkinson	Agriculture	61
2010	Region 2	Baldwin	Agriculture	44
2021	Region 2	Baldwin	Agriculture	31
2007	Region 2	Baldwin	Land Clearing	497
2010	Region 5	Appling	Land Clearing	1070
2012	Region 2	Baldwin	Agriculture	30
2022	Region 5	Atkinson	Land Clearing	3
2021	Region 2	Baldwin	Silviculture	70
2021	Region 5	Atkinson	Agriculture	44
2010	Region 2	Baldwin	Land Clearing	562
2009	Region 2	Baldwin	Land Clearing	758
2005	Region 2	Baldwin	Agriculture	4
2013	Region 5	Appling	Agriculture	237
2007	Region 2	Baldwin	Silviculture	6
2019	Region 5	Appling	Silviculture	237
2013	Region 5	Appling	Silviculture	473
2018	Region 5	Atkinson	Silviculture	140
2014	Region 5	Appling	Land Clearing	1003
2022	Region 5	Appling	Silviculture	5
2009	Region 5	Atkinson	Agriculture	6

Now, I want to get Previous 5 year Average Counts for each row.  For sure, the previous 5-year average needs to be for the same County and same Category. 

I can do the following ito Add a Column.

List.Sum(
Table.SelectRows(
#"Grouped Rows",
(C) => C[FiscalYear] <= ([FiscalYear] -1) and (C[FiscalYear]>=([FiscalYear]-5)) and C[CATEGORY]=[CATEGORY] and C[COUNTY]=[COUNTY]
)[Counts]
)/5 

However, by putting the above step in, it will extend the Load time from under 15 minutes to over 3 hours! 

So, I would like to try to use a Measure to do the same thing. 

MeasurePre5Year =

CALCULATE(
SUM('Table'[Permits])/5,
FILTER(
ALL('Table'),
'Table'[FiscalYear] >= (MAX(Table[FiscalYear])- 5)
&& 'Table'[FiscalYear] < MAX(Table[FiscalYear])
&& 'Table'[Category] = MAX(Table[Category])
&& 'Table'[Area] = MAX(Table[Area])

)
)

The above would work and would calculate the previous 5-year Average Count for the Area.  But, it will not break down into County.  The MeasurePre5Year will show the MeasurePre5Year for the Area under each County row within the same Area.  

Can a Measure do what I wanted, showing the pre-5year acount for each County and sum up in Area?  

Thanks.

dc7669

[DemoFour · Accepted solution]

Hay @dc7669  have a read through this function. DATESYTD - DAX Guide 

[dc7669]

Hi @DemoFour ,

Thanks for the DATESYTD - DAX Guide , it will help me learn DAX. 

Following the link you provided, I saw this.   

What if I want to break down the summary into Year AND Region AND/OR Category, how would I write the Measure? 

Using this DAX Code to write a measure on the data table above, 

MeasurePre5Year = 

CALCULATE(
SUM('Table'[Permits])/5,
FILTER(
ALL('Table'),
'Table'[FiscalYear] >= (MAX(Table[FiscalYear])- 5)
&& 'Table'[FiscalYear] < MAX(Table[FiscalYear])
&& 'Table'[Category] = MAX(Table[Category])
&& 'Table'[County] = MAX(Table[County])

)
)

I found that the roll up from County to Area will be using the last entry found in the County.  

Is it possible to write make the roll up to show Sum or Average instead of the last one?  

Thanks. 

dc7669

[dc7669 · Accepted solution]

FYI,  I think Measure will give us a value for each row.  But, if I want to roll up to show sum or average of a Meausre within a category, it may be impossible.   If I am wrong, please correct. 

So, the only thing I know to do is to use "Group By" in Transform.  

Then, roll up could be done easily.

Also, it seems to me that "Group by" steps often take a while to execute.  So, it is better to reduce the number of "Group by" steps in the calculation.

Further, it is better to create a View in SQL database to do the "Group By" and import the result into PowerBI, rather than have PowerBI do the "Group By" to reduce PowerBI's refresh time. 

dc7669

[DemoFour · Accepted solution]

Hay @dc7669  have a read through this function. DATESYTD - DAX Guide