Days between two dates. Kinda....

[die_kruzen]

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

[die_kruzen]

@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!!

[Ashish_Mathur]

Hi,

How exactly is the source data arranged?  Share that in a format that can be pasted in an MS Excel file.

[CoreyP · Accepted solution]

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] ) )

[die_kruzen]

@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.  

[CoreyP]

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 )

[CoreyP]

Pretty easy with data in the following format:

Create a calendar table and set up a relationship. Add a slicer using the calendar table.