Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a table with start and end of vacation. I need to generate reports with this data, but the subtraction of days (end-start) is being computed only in the beginning month.
Example
Name: João
Start: 01/25/2018
End: 02/06/2018
Reason: Holidays
Absent days: 13
I want a way that automatically distributes 7 days in January and 6 in February, in this example
Currently all 13 days of vacation are being entered in the beginning month, ie in January in this example. Thanks
Hi @Anonymous,
You can increase the granularity of the table to a date level, so you will have date for each absence then you can link your date dim table to this date and you should be able to slice and dice by week month or day.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mariusz Sorry for the ignorance, I am learning to use power BI, but I did not understand your answer. On your suggestion the missing days would be allocated separately, according to each month?
Hi @Anonymous
Currently your table has start and end dates, what you need is to increase the granularity to a date level like.
Current like below.
RecordID | start | end | type | name | UserID |
1 | 01/01/2019 | 12/01/2019 | Holiday | Mariusz | 0 |
2 | 10/01/2019 | 12/01/2019 | Holiday | Gustavo | 1 |
New Table
RecordID | Date | typeID | UserID |
1 | 01/01/2019 | Holiday | 0 |
1 | 02/01/2019 | Holiday | 0 |
1 | 03/01/2019 | Holiday | 0 |
1 | 04/01/2019 | Holiday | 0 |
1 | 05/01/2019 | Holiday | 0 |
1 | 06/01/2019 | Holiday | 0 |
1 | 07/01/2019 | Holiday | 0 |
1 | 08/01/2019 | Holiday | 0 |
1 | 09/01/2019 | Holiday | 0 |
1 | 10/01/2019 | Holiday | 0 |
1 | 11/01/2019 | Holiday | 0 |
1 | 12/01/2019 | Holiday | 0 |
2 | 10/01/2019 | Holiday | 1 |
2 | 11/01/2019 | Holiday | 1 |
2 | 12/01/2019 | Holiday | 1 |
then your months should be fine.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mariusz I appreciate the suggestion, it would really help. However, it becomes unfeasible for my case because there are occurrences with a difference of dates of 6 months or more. Dividing this period into lines would be very extensive. plus I'm analyzing data for four years. So far I can't think of another way to solve this problem.... thank you !
hi @hgzelaya Thank you for your reply. The difference of days I got, but how to divide the start dates of the holidays in one month and finish on the other? The goal is to have a count that sums exactly the number of days missing from each month
Hi @Anonymous ,
The difference of days I got, but how to divide the start dates of the holidays in one month and finish on the other? The goal is to have a count that sums exactly the number of days missing from each month
Would you please illustrate your expected output with examples?
Regards,
Yuliana Gu
When I create the chart "Total Vacation Hours Per Month" I have the value of 4,966.98 hours for January 2018.
However, if we add in the database (excel) all the holiday hours of January 2018 the real value is 2,248 hours.This is because power BI is allocating all the missing hours in the start month.
If I go on vacation from 20/01/2018 and return 03/02/2018 I need it to disappear in January only 11 days and in February 3 days. Currently my measure is adding up all 14 days of absence in January and this is not true. Did you understand?
Hi @Anonymous ,
Here are some similar links for your reference:
calculate number of working days between 2 dates
Calculating number of days between two dates
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |