Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
die_kruzen
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
CoreyP
Solution Sage
Solution Sage

Additionally, if your data is in the traditional format with from/to dates like this:

CoreyP_0-1700104975972.png

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] ) )
CoreyP_1-1700105064200.png

 

View solution in original post

6 REPLIES 6
die_kruzen
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!!

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
CoreyP
Solution Sage
Solution Sage

Additionally, if your data is in the traditional format with from/to dates like this:

CoreyP_0-1700104975972.png

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] ) )
CoreyP_1-1700105064200.png

 

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

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
Solution Sage
Solution Sage

Pretty easy with data in the following format:

CoreyP_0-1700088933192.png

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

CoreyP_1-1700088980708.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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