cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Difference of days between months

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

9 REPLIES 9
Highlighted
hgzelaya Regular Visitor
Regular Visitor

Re: Difference of days between months

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

Re: Difference of days between months

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
Not applicable

Re: Difference of days between months

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  

Anonymous
Not applicable

Re: Difference of days between months

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

Super User IV
Super User IV

Re: Difference of days between months

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 nameUserID
101/01/201912/01/2019HolidayMariusz0
210/01/201912/01/2019HolidayGustavo1


New Table

RecordID DatetypeIDUserID
101/01/2019Holiday0
102/01/2019Holiday0
103/01/2019Holiday0
104/01/2019Holiday0
105/01/2019Holiday0
106/01/2019Holiday0
107/01/2019Holiday0
108/01/2019Holiday0
109/01/2019Holiday0
110/01/2019Holiday0
111/01/2019Holiday0
112/01/2019Holiday0
210/01/2019Holiday1
211/01/2019Holiday1
212/01/2019Holiday1

 

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
Not applicable

Re: Difference of days between months

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

Microsoft v-yulgu-msft
Microsoft

Re: Difference of days between months

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Difference of days between months



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?

Microsoft v-yulgu-msft
Microsoft

Re: Difference of days between months

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors