I started with a data like this,
And then added the Index Column using the query editor,
And then created a column to check the number of times a single ID is present :-
Number of ID's Present = COUNTROWS(FILTER('MyTable',
(EARLIER('MyTable'[ID]) = 'MyTable'[ID])))
And then wanted to know the starting Index of each ID's to make it easy :-
Starting Index = CALCULATE(MIN(MyTable[Index]),FILTER('MyTable',
(EARLIER('MyTable'[ID]) = 'MyTable'[ID])))
And then now you can do what you are looking for with some easy ways,
First Date 2 =
Var NumberofIDsPresent = MyTable[Number of ID's Present]
Var StartingIndex = MyTable[Starting Index]
Var CurrentIndex = MyTable[Index]
Var Only_OneTime_ID_Present = IF(OR(NumberofIDsPresent = 1,CurrentIndex = StartingIndex),1,0)
Var Multiple_Times_ID_Present = CALCULATE(MIN(MyTable[First Date]),FILTER(MyTable, MyTable[Index] = CurrentIndex - 1))
var result = IF(Only_OneTime_ID_Present = 1, BLANK(),Multiple_Times_ID_Present)
return result
Last Date 2 =
Var NumberofIDsPresent = MyTable[Number of ID's Present]
Var StartingIndex = MyTable[Starting Index]
Var CurrentIndex = MyTable[Index]
Var Only_OneTime_ID_Present = IF(OR(NumberofIDsPresent = 1,CurrentIndex = StartingIndex),1,0)
Var Multiple_Times_ID_Present = CALCULATE(MIN(MyTable[Last Date]),FILTER(MyTable, MyTable[Index] = CurrentIndex - 1))
var result = IF(Only_OneTime_ID_Present = 1, BLANK(),Multiple_Times_ID_Present)
return result
Now, this makes my data look something like this,
Please accept the answer if it helps and Kindly let me know, if it doesn't solve your problem.
