cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Solution Sage

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 6
Frequent Visitor

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

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

Frequent Visitor

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

Solution Sage

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 )

Solution Sage

Pretty easy with data in the following format:

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors