Filtering ID & date compared within two date columns and many lines for this ID

[LithanaM]

Hi beautiful community, 

I have a question that I am not sure can be solved, but there are so many possibilities with PBI and so high skilled people in there that I am taking a chance 🙂

I am following several students stages with a beginning of stage date and end of stage date through different stages.

Below the exemple of one Student.

The data have been cumulated each year and unfortunately, I have some stages that were projected and did not happen that are still in the Database.

Date where in the column FinStage-RecMin will be above zero are projections, but some of them are to be kept them

For each selected student, I have to eliminate stages where the projected start dates (FinStage-RecMin>0) are already included in another stage for this student.

In the example below, I have to eliminate the line 28 juillet 2020 (FinStage-RecMin= 276) that is included in the stage 27 juillet to 27 September (in the line above).

Is there a way to do that?

Thank you all for your help!

Nathalie

[v-zhangti]

Hi, @LithanaM 

If you are trying to eliminate the (FinStage-RecMin>0) rows, you can try the following method.

Original table:

Table:

Table 2 = 
Var table1=SUMMARIZE('Table','Table'[Date],'Table'[FinStage-RecMin],'Table'[Stage])
Var table2=FILTER(table1,[FinStage-RecMin]<=0)
return
table2

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LithanaM]

Bonjour, 

Unfortunately, I can't just eliminate the >0 lines, because some of them are unique and have to be kept.

I actually work with historical data that were cumulated and where duplicates were eliminated, but it was not always the most recent entries that were kept, hence the problem I am having now....

I didn't have an example in this table, but I can have a finstage-recmin>0 but that does not exist in other instances and need to be kept.

Nathalie