Forum Discussion
Min & Max Value within a date range
Hi all,
I am trying to work out a Min and a Max however what I would like to do is use these measures for an axis in a line chart.
As an example I have a =SUM(SALES) and what I would like to do is find the Minimum Value of that SUM within the past 365 days and another measure for the Max value within those 365 days.
Hi BryceBicknell ,
Please try:
MIN_A = MINX(DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-365,DAY),[A])MAX_A = MAXX(DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-365,DAY),[A])Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
6 Replies
Yes I do I have both
Hi BryceBicknell ,
How do you intend to use the min and max values in a line chart - as a dimension or a fact? And what is your basis for calculating what dates are within 365 days? Can you please share sample data and your expected result.
What I want to do is use it for the Axis min and max range, yes I do have a date table.
Below is an example of the data what my Min value should be is 2 and max should be 18 within those dates below, the main difference is my data set has hundreds of stores. So smallest sum of all stores for the min and the largest sum of all stores for the max.
Store Number Store Name Store Open Date Area Region State Total Date Sales 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Wednesday, 2 August 2017 1 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Thursday, 3 August 2017 2 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Friday, 4 August 2017 3 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Saturday, 5 August 2017 4 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Sunday, 6 August 2017 5 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Monday, 7 August 2017 6 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Tuesday, 8 August 2017 7 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Wednesday, 9 August 2017 8 1234 ABC Friday, 29 April 2022 XXXX XXXX XXXX -159 Thursday, 10 August 2017 9 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Wednesday, 2 August 2017 1 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Thursday, 3 August 2017 2 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Friday, 4 August 2017 3 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Saturday, 5 August 2017 4 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Sunday, 6 August 2017 5 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Monday, 7 August 2017 6 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Tuesday, 8 August 2017 7 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Wednesday, 9 August 2017 8 12345 ABCD Friday, 29 April 2022 XXXX XXXX XXXX -159 Thursday, 10 August 2017 9 Hi BryceBicknell ,
Please try:
MIN_A = MINX(DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-365,DAY),[A])MAX_A = MAXX(DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-365,DAY),[A])Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Yes I do have a calendar table I also have dates in the tables I am pulling from but the below is linked
