Selecting one row based on multiple conditions

[nandamuri]

I 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.

[MFGF]

I 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.