Find Missing Dates

(0) Share

Report

Posted on by christian12

204

Hi,

Need help in finding missing dates.

At the moment I have two collections.

1st collection called DATE_INFO which has say the last 4 years of dates. Each line represents a date.

2nd collection is Read_Dates where each line will have different dates although in date order.

Example Read_Dates

Row 1: 2023-10-15

Row 2: 2023-08-10

Row 3: 2023-06-05

Row 4: 2022-12-21

Now in the column of Days_Missing in my Read_Dates collection i need to see if any days are missing against the DATE_INFO collection.

Example Read_Dates for row 1 will check the range from row 2 & 1 ( Start Date 2023-08-10 to End Date 2023-10-15) against DATE_INFO to see if all dates in the range are there. If not I need to update the Days_Missing with the number of dates missing.

This will need to be done on each row in the Read_Dates collection where the next row is the start date and the current row is the end date.

I've tried a few things although can't seem to get the correct outcome.

Any help is greatly appreciated.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

BrianS 2,407 Super User 2024 Season 1 on at

Like (0)

Report

I'm not where I can test this, but it should be close

Not sure what you need to do with the data, but to get the answers you will use ForAll to go thru the list of Read_Dates. Code would be similar to this - please excuse syntax errors - as I say I can't test it now.

Set(var_1, First(Read_Dates).Value;

ForAll(Read_Dates,

Set(var_2, ThisRecord.Value);

Set(var_DaysBetween, DateDiff(var_1,var_2) );

Patch(Somewhere, var_DaysBetween);

Set(var_1, var_2)

)

I hope this rough outline gets you where you need to go.

Was this reply helpful? Yes No
christian12 204 on at

Like (0)

Report

Thank you although I get an error.

Done some research and you cant use the set function within ForAll 🙁

The function can not be invoked within a ForAll.

Was this reply helpful? Yes No
BrianS 2,407 Super User 2024 Season 1 on at

Like (0)

Report

That's a bummer - What if you count the number of rows in the Date_Read collection and set a variable to that value (var_Size). Use the Index function in a ForAll to make a new collection, var_Size will make sure you don't try to go past the end of the Date_Read collection. In that ForAll read the current Item and the next item in the collection (with Index you can set the item to read as one more than the current item). Using that technique you can then Collect into a new collection with the current value and the next value in the same record. Then you can use DateDiff to get the time between those values.

Was this reply helpful? Yes No