Forum Discussion

BryceBicknell's avatar
BryceBicknell
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 Team

     

    If 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

danextian's avatar
danextian
Icon for Super User rankSuper User
4 years ago

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.

BryceBicknell's avatar
BryceBicknell
Frequent Visitor
4 years ago

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 NumberStore NameStore Open DateAreaRegionStateTotalDateSales
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Wednesday, 2 August 20171
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Thursday, 3 August 20172
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Friday, 4 August 20173
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Saturday, 5 August 20174
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Sunday, 6 August 20175
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Monday, 7 August 20176
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Tuesday, 8 August 20177
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Wednesday, 9 August 20178
1234ABCFriday, 29 April 2022XXXXXXXXXXXX-159Thursday, 10 August 20179
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Wednesday, 2 August 20171
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Thursday, 3 August 20172
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Friday, 4 August 20173
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Saturday, 5 August 20174
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Sunday, 6 August 20175
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Monday, 7 August 20176
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Tuesday, 8 August 20177
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Wednesday, 9 August 20178
12345ABCDFriday, 29 April 2022XXXXXXXXXXXX-159Thursday, 10 August 20179
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 Team

     

    If 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