The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
6 | |
6 |