Forum Discussion

EmmaLiuLiu's avatar
EmmaLiuLiu
Icon for Helper I rankHelper I
1 year ago
Solved

Count Number of Days between Two Days (Not Consecutive)

Hi, 

 

I have a data table with a column of different dates and I want to count the number of distinct days in there. I know I can create a column with Start Date and a column with End date then use DATEDIFF to calculate the number of days in between. However, the dates in the column are not consecutive. I've attached a sample column here. Can someone please help me? Thanks in advance!

Dates
1/1/2025
1/1/2025
1/3/2025
1/3/2025
1/3/2025
1/3/2025
1/4/2025
1/4/2025
1/10/2025

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thank for ryan_mayu and Irwan's concern about this issue.

     

    Hi, EmmaLiuLiu 

    I'm not sure I understand your needs:
    If you simply want to count the number of non-repeating dates in the date column, you can refer to the following Measure:

     

    DistinctDaysCount = DISTINCTCOUNT('Table'[Dates])

     

     

     


    If you are trying to calculate the number of days between the next two dates in the Date column, you can refer to the following calculated column:

     

    IntervalDays = 
VAR CurrentDate = 'Table'[Dates]
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[Dates]),
        FILTER(
            'Table',
            'Table'[Dates] < CurrentDate
        )
    )
RETURN
    IF(
        ISBLANK(PreviousDate),
        0,
        DATEDIFF(PreviousDate, CurrentDate, DAY) - 1
    )

     

     

     

    I have attached the pbix file for the above example below, hope it helps you.

     

    If none of the above methods solved your problem, please share the details of the result you are expecting and we will try our best to solve the problem you are facing.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    hello EmmaLiuLiu 

     

    i might be misunderstood but if the problem is non-consecutive day, is it possible to SUMMARIZE the table first?
    something like this in new table.

    Hope this will help.

    Thank you.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank for ryan_mayu and Irwan's concern about this issue.

     

    Hi, EmmaLiuLiu 

    I'm not sure I understand your needs:
    If you simply want to count the number of non-repeating dates in the date column, you can refer to the following Measure:

     

    DistinctDaysCount = DISTINCTCOUNT('Table'[Dates])

     

     

     


    If you are trying to calculate the number of days between the next two dates in the Date column, you can refer to the following calculated column:

     

    IntervalDays = 
VAR CurrentDate = 'Table'[Dates]
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[Dates]),
        FILTER(
            'Table',
            'Table'[Dates] < CurrentDate
        )
    )
RETURN
    IF(
        ISBLANK(PreviousDate),
        0,
        DATEDIFF(PreviousDate, CurrentDate, DAY) - 1
    )

     

     

     

    I have attached the pbix file for the above example below, hope it helps you.

     

    If none of the above methods solved your problem, please share the details of the result you are expecting and we will try our best to solve the problem you are facing.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.