Count Number of Days between Two Days (Not Consecutive)

[EmmaLiuLiu]

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

[Anonymous · Accepted solution]

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.

[ryan_mayu]

@EmmaLiuLiu 

what's the expected output based on the sample data you provided?

[Irwan]

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.