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

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
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

cobdeng's avatar
cobdeng
Frequent Visitor
9 years ago

Thanks Michael_Shao

 

That worked (save for a missing close bracket!!)

 

Many thanks