Forum Discussion
Need help with day count please
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
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.
2 Replies
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.
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.
