Forum Discussion
Days between two dates. Kinda....
Hello all. I have a leave calendar where the customer is asking for a report that returns the number of days from. a date range. Here is a scenario
Person A is on leave 11/01/2023 - 11/15/2023.
Person B is on leave 11/13/2023 - 11/20/23/2023
Person C is on leave 11/7/2023 - 11/9/2023
The customer runs a report for 11/10/2023 - 11/14/2023.
The result:
Person A = 4 days, Person B 1 day, Person C 0 days.
Is there a relative easy way to accomplish this? Seems like it might be rather difficult.
Any thoughts would be appreciated. Thank you
Additionally, if your data is in the traditional format with from/to dates like this:
You can transform it into a more easy to use format with a calculated table using DAX:
Leave Expanded = GENERATE( LeaveTable , CALENDAR( LeaveTable[From Date] , LeaveTable[To Date] ) )
6 Replies
Pretty easy with data in the following format:
Create a calendar table and set up a relationship. Add a slicer using the calendar table.
Additionally, if your data is in the traditional format with from/to dates like this:
You can transform it into a more easy to use format with a calculated table using DAX:
Leave Expanded = GENERATE( LeaveTable , CALENDAR( LeaveTable[From Date] , LeaveTable[To Date] ) )Hi,
How exactly is the source data arranged? Share that in a format that can be pasted in an MS Excel file.
Ashish_Mathur CoreyP -- thank you all. I was able to piece things together and make it work thanks to your help. Moreover I learned a lot from just this post in particular - the Generate command. That was extemely helpful. Again, thanks all!!
CoreyP thank you. Question though, your example is one date (if I understand correctly) and what I need is something that can pull the relevate days from a range. So, if I select 11/10 - 11/15 and there is a calendar entry for 11/12 - 11/18 I would only want three days out of that query returned. Not sure your example fits that criteria, but it could definately be my lack of understanding.
My example is not one date, it is a range. If you see in the screenshot, to the left of the table showing days on leave by person, there is a date slicer with the range of 11/10 - 11/14, per your first example. So, as you can see, with that range selected, person A has 5 days on leave. In your example, you had the result for person A of 4 days, and person B 1 day. I am getting 5 and 2, respectively, because each day in the range is counted. For example, if a person has a leave range of 11/10 - 11/14, that's 5 days, not 4. ( 11/10, 11/11/, 11/12, 11/13, 11/14 )
