Forum Discussion

cobdeng's avatar
cobdeng
Frequent Visitor
9 years ago
Solved

Extract correct Max/Min Values

I have a Sharepoint List which contains Locations and data input times - as below. Location Date FH 20/10/2016 00:00 FH 20/10/2016 12:00 FF 20/10/2016 01:00 FF 20/10/2016 10:00 ...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi cobdeng,

     

    This could be done by the following ways.

    We need to create another column (Or create another date table, then create the relationship between the date table and main tbale) to help calculate.

    YMD = format('Table'[ReportDate], "yyyy-mm-dd")

    Then add this column in earlier calculated column, for example:

    MaxDate = maxx(
                      filter(Sheet1, And(Sheet1[Location]=earlier(Sheet1[Location]),

                                                    Sheet1[YMD]=earlier(Sheet1[YMD])),
                      Sheet1[Date] )

    Check to see if this would work.

    Regards

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

cobdeng In pbi desktop go to query editor and under Transform tab use Group By as below image.

 

  • cobdeng's avatar
    cobdeng
    Frequent Visitor
    9 years ago

    Not sure that will give me the right answers as there is other data as well apart from the dates

    • v-micsh-msft's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi cobdeng,

       

      In addition to what suggested by ankitpatira, we could also take a try with the method below:

      1. First sort the table by Location in Power BI Desktop, this would bring all the same location together,

      2. Under Modeling Tab, click Add a column,  with the following formula:

      MinDate = minx(
                  filter(Sheet1, Sheet1[Location]=earlier(Sheet1[Location])), 
                  Sheet1[Date] )

      3. Click the Add a column again, with the formula below:

      MaxDate = maxx(
                  filter(Sheet1, Sheet1[Location]=earlier(Sheet1[Location])), 
                  Sheet1[Date] )

      4. Check my testing result:

      If you need any further assistance on this, please post back.

      Regards

      • cobdeng's avatar
        cobdeng
        Frequent Visitor
        9 years ago

        Thanks Michael_Shao

         

        Nearly, but not quite as we can have multiple dates/times as per the screenshot below

         

         

        Where there are multiple dates/times, I need to identify the maximum and minimum date time on each individual date, so, for location 2 in the above, on 19/10/2016 both max and min should be 19/10/2016 11:00 but on 20/10/2016 minimum should be 20/10/2016 01:00 and the maximum 20/10/2016 11:00.  For location 4, on 19/10/2016 min/max should be 19/10/2016 00:00 - 19/10/2016 12:00, and on 20/10/2016  min/max should be 20/10/2016 00:00 - 20/10/2016 12:00.

         

        Thanks in advance