Logic based on dates interaction in PowerQuery

[ValeriaBreve]

Hello!

I need to run a specific logic in one of my queries.

I have a column coming from the database: "Last Updated On" which is the last time the users sent the data modifications to the database.

And then I have a column "Activity Start Date", with date and time of when the activity will start.

The logic is that the activity start date should be classified as "Include" only if:

1) the Activity Start Date is greater than the corresponding "Last Updated On" (this is the easy part ;-)) AND

2) the Activity start date is smaller than the next "Last Updated On" in chronological order.

I have tried to summarize below:

Could you please help me to understand how to reach the second condition?

Thanks!!!!

Kind regards

Valeria

[ValeriaBreve · Accepted solution]

I think I figured this one out... I added a ranking column on the table on the Last Updated On Column (Table.AddRankColumn(#"Filtered Rows1","Rank",{"LAST_UPDATED_ON", Order.Ascending},[RankKind = RankKind.Dense])

Then, I duplicated the query, grouped by "Last Updated On" date and "Rank", and then added a custom column "Rank -1". I then merged this query with the original one on the Rank/Rank-1 column to get the next available "Last Updated On" date, from here it's easy.

Please chime in if you have a better solution!

Thanks

Kind regards

Valeria