Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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 |
Solved! Go to 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
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 |
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
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
19 | |
14 | |
8 | |
7 |