Forum Discussion

juan_botero8's avatar
juan_botero8
New Member
2 years ago
Solved

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.

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

Need Help
  • danextian's avatar
    danextian
    2 years ago

    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.

     

7 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      juan_botero8
      New Member
      2 years ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        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.

         

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    1 year ago

    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.