Forum Discussion
Calculate days between two dates without duplicating days
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.
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
Super UserI 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.
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.
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!