Forum Discussion
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 |
| FF | 20/10/2016 11:00 |
| GCC | 20/10/2016 00:00 |
| GCC | 20/10/2016 12:00 |
| JC | 20/10/2016 00:00 |
| JC | 20/10/2016 12:00 |
| MH | 20/10/2016 01:00 |
| MH | 20/10/2016 12:00 |
| ML | 20/10/2016 00:00 |
| ML | 20/10/2016 12:00 |
| NV | 20/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
| Location | Date | Loc Min Date | Loc Max Date |
| FH | 20/10/2016 00:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| FH | 20/10/2016 12:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| FF | 20/10/2016 01:00 | 20/10/2016 01:00 | 20/10/2016 11:00 |
| FF | 20/10/2016 10:00 | 20/10/2016 01:00 | 20/10/2016 11:00 |
| FF | 20/10/2016 11:00 | 20/10/2016 01:00 | 20/10/2016 11:00 |
| GCC | 20/10/2016 00:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| GCC | 20/10/2016 12:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| JC | 20/10/2016 00:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| JC | 20/10/2016 12:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| MH | 20/10/2016 01:00 | 20/10/2016 01:00 | 20/10/2016 12:00 |
| MH | 20/10/2016 12:00 | 20/10/2016 01:00 | 20/10/2016 12:00 |
| ML | 20/10/2016 00:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| ML | 20/10/2016 12:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| NV | 20/10/2016 00:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
| NV | 20/10/2016 12:00 | 20/10/2016 00:00 | 20/10/2016 12:00 |
Is anybody able to assist as I am currently struggling as this is all very new to me
Thanks
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
6 Replies
Not sure that will give me the right answers as there is other data as well apart from the dates
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
