Generate Mins and Maxes of Data Range (By category and week of year) to Utilize Error Bands

[MatthewJM]

Hello!

I have a dataset that is very extensive with many categories (and some calculated measures), I would like to create a measure or a data table that will create mins and maxes of each category by week, over a set time period (say last 3 years). I have the data mapped to a date table and use it to create "seasonality" graphs that overlays data, each year being a line, and a different chart for each category. 

Then I would like to chart the most recent data as a line on a graph and overlay that with last 3 years average appearing as a shaded region on the graph. I think I can do this once I have a table of values. However, I calculate that in order to do this for my current dataset it would be 84 columns. Is there an easy way to do this or am I forced to brute force it? And if I have to brute force it, how can I use a measure in a column of a power query? 

Below is a sample data set to help the conversation. 

Thank you for your help!

Category	Week of Year	Year	Value
Category 1	1	2022	5
Category 2	1	2022	50
Category 1	2	2022	1
Category 2	2	2022	300
Category 1	1	2021	2
Category 2	1	2021	400
Category 1	2	2021	8
Category 2	2	2021	100
Category 1	1	2020	7
Category 2	1	2020	200
Category 1	2	2020	3
Category 2	2	2020	200

Goal	Category 1 Min	Category 1 Max	Category 2 Min	Category 2 Max
Week1	2	7	100	300
Week2	1	8	50	400

[MatthewJM]

Thank you for the response. 

I am getting the desired result when the page filters are wider than this year - 5 years. For some reason the page filters are still effecting the data, even when I utilize the all function to remove any filters from the data set and do all the filtering in the measure. 

And yes I do have a date table that is separate from where my data lives, however I was just trying to mess around with different filters, neither filtering with the date filter nor with the data table works. 

Thanks!

[MatthewJM]

I also tried to use the page filters to my advantage and use the allexcept function to remove all filters except those on the map descriptors table, and that solution was giving me the same desired result when greater than 5 years was selected, but not when only this year was selected. 

thanks,

[v-zhangti]

Hi, @MatthewJM 

You can try the following methods.

Max = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Category],'Table'[Week of Year]))

Min = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Category],'Table'[Week of Year]))

Is this the result you expect? If not, please provide your desired outcome.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MatthewJM]

I would like the result to look like this. With the current data set being the only line on the graph, and use error bands to shade the region.