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