Need help with day count please

[RichOB]

I have a dog shelter and I need to count the days that each kennel is empty. Each row is the duration that a dog is in a kennel.

I need the difference between the end date of a dog leaving the kennel and the start date of the next dog joining. If there is no start date of the next dog joining, I need Today's date to be calculated for the day count please.

For example, using the table below:

Kennel 1 = 0 days

Kennel 2 = 285 days

Kennel 3 = 186 days 

Kennel 4 = 0 days

Kennel	Dog	Start Date	End Date
1	Max	24/02/2024	04/03/2024
1	Charles	04/03/2024	25/07/2024
1	Lila	25/07/2024
2	Milo	01/06/2024	26/08/2024
3	Rex	08/09/2024	10/10/2024
4	Birdie	01/11/2024	30/12/2024
4	Bob	30/12/2024

Thanks

[v-menakakota]

Hi @RichOB ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin · Accepted solution]

Can I borrow your calendar for a minute?  I would like some extra days...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY69CoQwEIRf5Ugd2J+op+1deza2YhG5gIGAoI2P78agEmEJzMy3k+17RUqr1m7ycgHIwMiFCBRhkhh0or6TXYJb81DWSsB3Tv58sHmi1etIOf7mwxxLCLC6SirA+i4xYnUuHiUuNidFCDIXFa2PX/7epTqiEzQC8gOcxzw4Thp2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kennel = _t, Dog = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Kennel"}, {{"Rows", each _, type table [Kennel=nullable text, Dog=nullable text, Start Date=nullable date, #"End Date "=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Kennel Empty Days", each Int64.From(if Table.RowCount([Rows])>1 then [Rows]{0}[Start Date] - [Rows]{1}[End Date] else Date.From(DateTime.LocalNow()) - [Rows]{0}[End Date]),Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.