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
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
Solved! Go to 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:
@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!!
Hi,
How exactly is the source data arranged? Share that in a format that can be pasted in an MS Excel file.
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:
@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 )
Pretty easy with data in the following format:
Create a calendar table and set up a relationship. Add a slicer using the calendar table.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
109 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |