Count how many times a date appears between start date and end date

[Anonymous]

Hello,

I'm new to DAX. I hope you can help?

I have a table with start date and end date columns. I would like to add a column that counts how many times the start date from other rows in the table appear between the start date and end date of each row and if the start date doesn't appear, it then checks if the end date appears between the start and end date in each row.

Is the formula for this? It basically an overlap of dates and times

Thanks

Joe

Start Date                              End Date                                      Count

01/01/2018 00:00:00              01/01/2018 01:45:00                        4

01/01/2018 00:00:00              01/01/2018 01:45:00                        4

01/01/2018 00:00:00              01/01/2018 01:45:00                        4

01/01/2018 01:00:00              01/01/2018 02:45:00                        5

01/01/2018 02:00:00              01/01/2018 03:45:00                        2

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this Column

Count Column =
COUNTROWS (
    FILTER (
        table1,
        OR (
            [Start Date] >= EARLIER ( [Start Date] )
                && [Start Date] <= EARLIER ( [End Date] ),
            [End Date] >= EARLIER ( [Start Date] )
                && [End Date] <= EARLIER ( [End Date] )
        )
    )
)

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this Column

Count Column =
COUNTROWS (
    FILTER (
        table1,
        OR (
            [Start Date] >= EARLIER ( [Start Date] )
                && [Start Date] <= EARLIER ( [End Date] ),
            [End Date] >= EARLIER ( [Start Date] )
                && [End Date] <= EARLIER ( [End Date] )
        )
    )
)

[Anonymous]

Hello Zubair

If you have the time,

I would like to show in another column the same result, but counting with the ID within the timeframe. Basically the formula, but showing the filterd results based on the ID

ID                                   Start Date                              End Date                               CountAll Dates        Count ID

Bag                            01/01/2018 00:00:00             01/01/2018 01:45:00                        4                            2

Bag                            01/01/2018 00:00:00             01/01/2018 01:45:00                        4                            2

Hat                            01/01/2018 00:00:00              01/01/2018 01:45:00                        4                           1

Hat                            01/01/2018 01:00:00              01/01/2018 02:45:00                        5                           2

Hat                            01/01/2018 02:00:00              01/01/2018 03:45:00                        2                           2

Kind regards

Joe

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Hello Joe

You can use this revised formula

Column 2 =
COUNTROWS (
    FILTER (
        table1,
        AND (
            OR (
                [Start Date] >= EARLIER ( [Start Date] )
                    && [Start Date] <= EARLIER ( [End Date] ),
                [End Date] >= EARLIER ( [Start Date] )
                    && [End Date] <= EARLIER ( [End Date] )
            ),
            [ID] = EARLIER ( [ID] )
        )
    )
)

[Anonymous]

Hi Zubair

It worked perfectly, thank you so much! 

Kind regards

Joe

[Anonymous]

Thank you Zubair

You have thought me two lessons. 

1. The formula

2. To ask for help sooner 🙂

Kind regards

Joe