Calculate days between two dates without duplicating days

[juan_botero8]

Hi all!

I have created a matrix with a summary of information, it has two date fields and a calculation of days using the DATEDIFF function. The problem is that I need to count the days without duplicating the days.

As you can see in the table, for example row 2 and 3 overlap in the whole month of February of 1995, but I need to consider only 28 days of February, not 56 (obviously because February had 28 days in 1995). I have thought of creating a new table with the sequence of dates for each of the companies and then count distinct days, but I don't know if that will be the solution and maybe is overcomplicating the things.

I appreciate a lot your help.

Thanks,

Juan

[Syndicate_Admin]

Hi John, your idea is going in the right direction: the best solution is to create a table with all the unique dates between the start and end dates by company, and then count the different days. You can do this with DAX using GENERATE and CALENDAR:

DAX
Unique Days =
SUMMARIZE(
GENERATE(
Board
CALENDAR(Table[StartDate], Table[EndDate])
),
Table[Company], [Date]
)
You then simply count the different values of [Date] per company to avoid duplicates, ensuring that overlapping days are not added twice.

[danextian]

Hi @juan_botero8 

I'm trying to wrap my head around but just can't understand your explanation.  I'm not seeing 56 anywhere and it appears that datediff is calculating correctly.

[juan_botero8]

Hi! Sorry for not explaining me better.

Taking as the example rows 2 and 3, you will be able to see that:

• Row 2: Has the periods from January 1st, 1995 to June 30th, 2003.
• Row 3: Has the periods from February 1st, 1995, to February 28th, 1995.
• Row 3 is exactly 1 month, the full month of February from 2003, but as you can see, row 2 has also that period since it starts 1 month before.

The thing here is that the sample data relates to jobs a person had in the past. In the example, that person had 2 jobs for the same period (1 Feb to 28 Feb, 1995), but the days worked remain as 28. If we sum rows 2 and 3, we will be duplicating days for February, but that's incorrect because the person didn't work double, he worked 28 days in two (or more than two) different jobs during the same period.

So my problem is that I need to avoid duplicating the days, but with the current sum I'm not able to prevent the duplication.

I hope this illustrates better my problem.

Thanks a lot for replying to my message and hopefully we will reach to a solution!

Juan

[danextian · Accepted solution]

I can't think of anything else other than using PQ to create rows of dates using the  from and to date columns and then get the distinct count of those new dates so they are not counted multiple times in the total. Please see sample pbix.

[juan_botero8]

Hey! The Power Query solution worked as desired! In the end, it was a matter of adding all the dates, thanks to you and Power Query, that was done automatically 😅.

[danextian]

My only worry about that approach is the refresh might take a very long time on a large table. For a few thousand rows, the latency if there is should be negligible.

[juan_botero8]

That's true. But I guess it is anyway a good approach since for other customers, I might be facing the same. I will start using a single file per customer and if needed, create a summary file in Excel. Thank you!