The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |