cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cobdeng Frequent Visitor
Frequent Visitor

Extract correct Max/Min Values

I have a Sharepoint List which contains Locations and data input times - as below.

LocationDate
FH20/10/2016 00:00
FH20/10/2016 12:00
FF20/10/2016 01:00
FF20/10/2016 10:00
FF20/10/2016 11:00
GCC20/10/2016 00:00
GCC20/10/2016 12:00
JC20/10/2016 00:00
JC20/10/2016 12:00
MH20/10/2016 01:00
MH20/10/2016 12:00
ML20/10/2016 00:00
ML20/10/2016 12:00
NV20/10/2016 00:00
NV

20/10/2016 12:00

Within Power BI I need to be able to identify - as a Calculated Column - the Maximum and Minimum Dates per Location so it looks like the below

LocationDateLoc Min DateLoc Max Date
FH20/10/2016 00:0020/10/2016 00:0020/10/2016 12:00
FH20/10/2016 12:0020/10/2016 00:0020/10/2016 12:00
FF20/10/2016 01:0020/10/2016 01:0020/10/2016 11:00
FF20/10/2016 10:0020/10/2016 01:0020/10/2016 11:00
FF20/10/2016 11:0020/10/2016 01:0020/10/2016 11:00
GCC20/10/2016 00:0020/10/2016 00:0020/10/2016 12:00
GCC20/10/2016 12:0020/10/2016 00:0020/10/2016 12:00
JC20/10/2016 00:0020/10/2016 00:0020/10/2016 12:00
JC20/10/2016 12:0020/10/2016 00:0020/10/2016 12:00
MH20/10/2016 01:0020/10/2016 01:0020/10/2016 12:00
MH20/10/2016 12:0020/10/2016 01:0020/10/2016 12:00
ML20/10/2016 00:0020/10/2016 00:0020/10/2016 12:00
ML20/10/2016 12:0020/10/2016 00:0020/10/2016 12:00
NV20/10/2016 00:0020/10/2016 00:0020/10/2016 12:00
NV20/10/2016 12:0020/10/2016 00:0020/10/2016 12:00

 

Is anybody able to assist as I am currently struggling as this is all very new to me

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-micsh-msft
Microsoft

Re: Extract correct Max/Min Values

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

View solution in original post

6 REPLIES 6
ankitpatira Super Contributor
Super Contributor

Re: Extract correct Max/Min Values

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

 

Capture.PNG

cobdeng Frequent Visitor
Frequent Visitor

Re: Extract correct Max/Min Values

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

Microsoft v-micsh-msft
Microsoft

Re: Extract correct Max/Min Values

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:

23.PNG

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

Regards

cobdeng Frequent Visitor
Frequent Visitor

Re: Extract correct Max/Min Values

Thanks Michael_Shao

 

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

 

snip.png

 

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

Microsoft v-micsh-msft
Microsoft

Re: Extract correct Max/Min Values

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

View solution in original post

cobdeng Frequent Visitor
Frequent Visitor

Re: Extract correct Max/Min Values

Thanks Michael_Shao

 

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

 

Many thanks

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)