Counting consecutive dates

[BlueBird]

Need to count consecutive dates between a date range per person. the columns are: signature date, admit date, discharge date, person id. want to count consecutive signature dates between admit date and discharge date per person id. when person id changes start count over.

[Syndicate_Admin]

Hope this helps!

Set(currentCount, 0);
Set(previousPersonId, Blank());

ForAll(
SortByColumns(YourDataSource, "PersonId", "SignatureDate"), // Replace with your data source
If(
ThisRecord.PersonId <> previousPersonId,
Set(currentCount, 0)
);

If(
DateDiff(previousRecord.SignatureDate, ThisRecord.SignatureDate, Days) = 1,
Set(currentCount, currentCount + 1),
Set(currentCount, 1)
);

// Your additional logic using the currentCount

Set(previousPersonId, ThisRecord.PersonId)
)

[Anonymous · Accepted solution]

Hi @BlueBird ,

I tried creating the sample data based on your description.

Here's my solution.

1.Group by person id column.

Select All Rows.

2.Add an index column.

3.Expand it.

4.And the index column is counting consecutive dates. You can rename it.

Best Regards,

Stephen Tao

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

[Anonymous]

Ok so in that case we really don't need the admit and discharge date. We can just group by the person id and the signature date, and add the GroupKind.Local parameter to your formula, right before the closing parentheses in your Table.GroupBy function. This will group all person ids with contiguous dates. Use the "All Rows" aggregation if you want to keep your other columns, or just use min/ max on the admit/discharge.

--Nate 

[BlueBird]

Unfortunately I do need admit and discharge date - -I want to count the consecutive dates  - - comparing date to the previous date. admit and discharge date plays a role since someone could have multiple admin and discharge date.

[Anonymous]

= Table.AddColumn(PriorStepOrTableName, "Count", each Duration.Days([Discharge Date] - [Admit Date]), Int64.Tyoe)

--Nate

[BlueBird]

Thank you Nate. I tried using your answer and it did not work.

[BlueBird]

Thank you for your response. I am actually looking for the number of consecutive signature dates between the admit date and discharge date per person id.

[BlueBird]

For example, if person id 1234 was admitted on 11/01/2023 and discharged on 11/05/2023 and signed a document on 11/01/2023, then another document on 11/02/2023 and another document on 11/05/2023. The answer would be 2 consecutive dates. the document on 11/05/2023 would not count. Also, when the person id changes the counts starts over.  I hope that makes sense.

[BlueBird]

I hope someone can help me with coming up with solution.