Forum Discussion
Date range based on group column
| Id | Date | Amount |
| 1 | 05/05/2019 | 50 |
| 1 | 10/05/2019 | 200 |
| 1 | 15/05/2019 | 200 |
| 1 | 20/05/2019 | 200 |
| 1 | 21/05/2019 | 200 |
| 2 | 05/05/2019 | 100 |
| 2 | 10/05/2019 | 150 |
| 2 | 15/05/2019 | 115 |
| 2 | 20/05/2019 | 150 |
| 3 | 05/05/2019 | 200 |
| 3 | 10/05/2019 | 75 |
| 3 | 15/05/2019 | 75 |
| 4 | 20/05/2019 | 150 |
| 5 | 10/05/2019 | 200 |
| 5 | 20/05/2019 | 125 |
| 6 | 05/05/2019 | 100 |
Based on the [date] and [ID] columns is there a way to get a date range per ID:
| ID | Date Range |
| 1 | 05/05/2019 - 21/05/2019 |
| 2 | 05/05/2019 - 20/05/2019 |
| 3 | 05/05/2019 - 15/05/2019 |
| 4 | 20/05/2019 - 20/05/2019 |
| 5 | 10/05/2019 - 20/05/2019 |
| 6 | 05/05/2019 - 05/05/2019 |
I've tried adding a new column but it doesnt respect the ID column:
Column = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Max Date])) & "-" & CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Max Date]))Hi Anonymous
Add a measure like this to you visual.
Date Range_ =var _ID = SELECTEDVALUE(Sheet1[ID])var _MinDate = CALCULATE(MIN(Sheet1[Date]), Sheet1[ID] = _ID)var _MaxDate = CALCULATE(MAX(Sheet1[Date]), Sheet1[ID] = _ID)ReturnCONCATENATE(CONCATENATE(_MinDate, " - "), _MaxDate)
1 Reply
Hi Anonymous
Add a measure like this to you visual.
Date Range_ =var _ID = SELECTEDVALUE(Sheet1[ID])var _MinDate = CALCULATE(MIN(Sheet1[Date]), Sheet1[ID] = _ID)var _MaxDate = CALCULATE(MAX(Sheet1[Date]), Sheet1[ID] = _ID)ReturnCONCATENATE(CONCATENATE(_MinDate, " - "), _MaxDate)