Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juan_botero8
New Member

Calculate days between two dates without duplicating days

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.

juan_botero8_0-1716259961875.png

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

1 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.

danextian_0-1716353869353.pngdanextian_1-1716353879227.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

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.

danextian_0-1716353869353.pngdanextian_1-1716353879227.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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 😅.

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors