Difference of days between months

[Anonymous]

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

[Mariusz]

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.

[Anonymous]

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

[Mariusz]

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. 

[Anonymous]

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

[hgzelaya]

Hi, use this in a new column: DATEDIFF(Table[start.date];table[end.date];day) this will give you the difference in days

[Anonymous]

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  

[v-yulgu-msft]

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

[Anonymous]

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?

[v-yulgu-msft]

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