Quote from: nandamuri on 26 Feb 2024 12:18:08 PMI have a data that represents like this -
Employee | TYPE | Date Eff | Date End
A | No Data | 6/1/2022 | 7/31/2022
A | Data | 8/1/2022 | 12/1/2022
B | No Data | 5/1/2022 | 5/31/2022
B | Data1 | 6/1/2022 | 7/31/2022
B | Data2 | 8/1/2022 | 12/1/2022
Output should look like --
A 8/1/2022 12/1/2022
B 6/1/2022 12/1/2022
We are looking for start and end of periods that have data.
Firstly, add a filter to return rows that only have data
[Type] = 'Data'
Then you want to get the minimum Date Eff and the maximum Date End for each employee. Add a calculated item for each of these:
minimum([Date Eff] for [Employee])
and
maximum([Date End] for [Employee])
Then in your list, include the Employee item and the two calculated items.
Cheers!
MF.