Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
I did my best to wade through the board but could not find, or at least did not understand a way to solve my problem.
I am using a GANTT chart for project follow up and would need to create a column for start dates, which should come directly from end dates of previous project phases. "G" means Gate, so same project will have several Gates and each Gate's start date should be the end date of the previous Gate's end date. I can only get the end date from the data, so start date should be created in PBI.
This is what I mean:
Project name & Gate | End date | Start date |
Square G1 | 1.1.2020 | X |
Square G2 | 15.2.2020 | 1.1.2020 |
Square G3 | 20.3.2020 | 15.2.2020 |
Square G4 | 15.4.2020 | 20.3.2020 |
Square G5 | 10.6.2020 | 15.4.2020 |
Round G1 | 15.1.2020 | X |
Round G2 | 25.1.2020 | 15.1.2020 |
Round G3 | 30.3.2020 | 25.1.2020 |
Round G4 | 15.5.2020 | 30.3.2020 |
Round G5 | 12.6.2020 | 15.5.2020 |
Rectangle G1 | 3.3.2020 | X |
Rectangle G2 | 12.3.2020 | 3.3.2020 |
Rectangle G3 | 25.5.2020 | 12.3.2020 |
Rectangle G4 | 15.6.2020 | 25.5.2020 |
Rectangle G5 | 15.7.2020 | 15.6.2020 |
15.7.2020 |
Fields marked with X I would need to look up from other column named "Creation date".
Thank you in advance on all input and support. BR,
Karolus
Solved! Go to Solution.
Dear @KKarlsson ,
try this
HI @KKarlsson,
You can try to use following calculate column to extract value from your column as category and lookup the previous date.
StartDate =
CALCULATE (
MAX ( T2[End date] ),
FILTER (
ALLSELECTED ( T2 ),
PATHITEM ( SUBSTITUTE ( [Project name & Gate], " ", "|" ), 1 )
= PATHITEM ( SUBSTITUTE ( EARLIER ( [Project name & Gate] ), " ", "|" ), 1 )
&& [End date] < EARLIER ( T2[End date] )
)
)
Notice: end date should be a date type field or the above formula will get the wrong result. you can use custom format on the date field to setting display format.
Regards,
Xiaoxin Sheng
HI @KKarlsson,
You can try to use following calculate column to extract value from your column as category and lookup the previous date.
StartDate =
CALCULATE (
MAX ( T2[End date] ),
FILTER (
ALLSELECTED ( T2 ),
PATHITEM ( SUBSTITUTE ( [Project name & Gate], " ", "|" ), 1 )
= PATHITEM ( SUBSTITUTE ( EARLIER ( [Project name & Gate] ), " ", "|" ), 1 )
&& [End date] < EARLIER ( T2[End date] )
)
)
Notice: end date should be a date type field or the above formula will get the wrong result. you can use custom format on the date field to setting display format.
Regards,
Xiaoxin Sheng
Dear @KKarlsson ,
try this
@KKarlsson , based on what I got you need to calculate the start date
start date = maxx(filter(Table, [Project name] = earlier([Project name]) && [End date] <earlier([End date])),[End date])
Hi,
Sorry to resurrect an old forum but how would you go about this if you had the start date and wanted to calcutate the end date?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
120 | |
105 | |
77 | |
74 | |
52 |
User | Count |
---|---|
145 | |
109 | |
107 | |
90 | |
64 |