Forum Discussion

BlueBird's avatar
BlueBird
Frequent Visitor
2 years ago
Solved

Counting consecutive dates

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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

 

--Nate

BlueBird's avatar
BlueBird
Frequent Visitor
2 years ago

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's avatar
    BlueBird
    Frequent Visitor
    2 years ago

    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's avatar
      BlueBird
      Frequent Visitor
      2 years ago

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