Need Help Creating Start and End Date Column in Power BI

[Leslie1015]

Hi everyone,

I'm new to power BI, can somebody help me on how to create a start and end date in power BI.

This is the data I have

Date

Wed, 01 Mar 2023 07:00:53

Wed, 01 Mar 2023 07:01:12

Wed, 01 Mar 2023 07:01:23

Wed, 01 Mar 2023 07:01:31

Wed, 01 Mar 2023 07:07:30

Wed, 01 Mar 2023 07:09:06

I want the output to be like this

Date	Start date	End Date
Wed, 01 Mar 2023 07:00:53	Wed, 01 Mar 2023 07:00:53	Wed, 01 Mar 2023 07:01:12
Wed, 01 Mar 2023 07:01:12	Wed, 01 Mar 2023 07:01:12	Wed, 01 Mar 2023 07:01:23
Wed, 01 Mar 2023 07:01:23	Wed, 01 Mar 2023 07:01:23	Wed, 01 Mar 2023 07:01:31
Wed, 01 Mar 2023 07:01:31	Wed, 01 Mar 2023 07:01:31	Wed, 01 Mar 2023 07:07:30
Wed, 01 Mar 2023 07:07:30	Wed, 01 Mar 2023 07:07:30
Wed, 01 Mar 2023 07:09:06	Wed, 01 Mar 2023 07:09:06

[Leslie1015]

i forgot to add the Index Field. So, Ihave the date and the Index. I just need to get atleast the End Date. Can somebody help me please

		Output I want
Date	Index	Start Date	End Date
Wed, 01 Mar 2023 07:00:53	0	Wed, 01 Mar 2023 07:00:53	Wed, 01 Mar 2023 07:01:12
Wed, 01 Mar 2023 07:01:12	1	Wed, 01 Mar 2023 07:01:12	Wed, 01 Mar 2023 07:01:23
Wed, 01 Mar 2023 07:01:23	2	Wed, 01 Mar 2023 07:01:23	Wed, 01 Mar 2023 07:01:31
Wed, 01 Mar 2023 07:01:31	3	Wed, 01 Mar 2023 07:01:31	Wed, 01 Mar 2023 07:07:30
Wed, 01 Mar 2023 07:07:30	4	Wed, 01 Mar 2023 07:07:30	Wed, 01 Mar 2023 07:09:06
Wed, 01 Mar 2023 07:09:06	5	Wed, 01 Mar 2023 07:09:06

[v-shex-msft · Accepted solution]

Hi @Leslie1015,

You can create two calculate columns to achieve your requirement. The start date reference from the raw date field, the end date use current index field value to lookup the next row date value.

StartDate= 'Table'[Date]

EndDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Index] = EARLIER('Table'[Index]) + 1
        )
    )

Regards,

Xiaoxin Sheng