Forum Discussion
Anonymous
5 years agoNot applicable
Finding first date in table where column changes value
I have a table with a date column and a number column.
Example:
| Date | Number |
| 01-01-'21 | 4 |
| 02-01-'21 | 4 |
| 03-01-'21 | 2 |
I want to add a calculated column that returns the first date where the number is different than the current row.
I've tried using the following DAX formula:
Column =
CALCULATE(
MIN('Table'[Date].[Date]),
FILTER('Table', 'Table'[Date].[Date] > EARLIER('Table'[Date].[Date])),
FILTER('Table', 'Table'[Number] <> EARLIER('Table'[Number]))
)
As well as:
CALCULATE(
MIN('Table'[Date].[Date]),
FILTER('Table', 'Table'[Date].[Date] > EARLIER('Table'[Date].[Date])
&& 'Table'[Number] <> EARLIER('Table'[Number]))
)
But this only returns the first date after the current date:
| Date | Number | Column |
| 01-01-'21 | 4 | 02-01-'21 |
| 02-01-'21 | 4 | 03-01-'21 |
| 03-01-'21 | 2 | 04-01-'21 |
And the result I'm looking for is:
| Date | Number | Column |
| 01-01-'21 | 4 | 03-01-'21 |
| 02-01-'21 | 4 | 03-01-'21 |
| 03-01-'21 | 2 | Some date in the future where [Number] changes |
Somehow it feels like the second filter in the formula is being ignored.
Any help on how to accomplish this would be greatly appreciated.
Anonymous
Add the following Calc Column:Number Change = var __num = [Number] var __date = [Date] return CALCULATE( MIN(Table3[Date]), Table3[Number] <> __num, Table3[Date] > __date, REMOVEFILTERS(Table3) )
2 Replies
- FowmySuper User
Anonymous
Add the following Calc Column:Number Change = var __num = [Number] var __date = [Date] return CALCULATE( MIN(Table3[Date]), Table3[Number] <> __num, Table3[Date] > __date, REMOVEFILTERS(Table3) ) - CNENFRNLCommunity Champion
Changing Date = MINX ( FILTER ( INFO, INFO[Date] > EARLIER ( INFO[Date] ) && INFO[Value] <> EARLIER ( INFO[Value] ) ), INFO[Date] )