Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Power BI Community,
I am currently working on a project and facing a challenge in creating a TaskStartDate column in my Power BI dataset. I have multiple tasks for each project, and I need to sort them based on their finish date. Additionally, I want to select only those tasks where the milestone is not blank.
Here is a brief overview of the requirements:
I have attempted to implement this logic using Power Query, but I am encountering difficulties. Your guidance on how to achieve this in Power BI would be highly appreciated.
Best regards, KT
@Anonymous
Please provide an Excel file with some dummy data and the expected results with examples. You may attach the file link here after saving in Google or One drive.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
For example: If the task finish date from the first task of one project is 28/1/2023 then start date for the second task of that project will be the next day of the previous task finish date. i.e 29/1/2023
and taskstartdate for for the first task of the project will be same as the finishdate.
below sharing example
This is how data will look when i will remove milestone is blank, mile stone data is bit different i have just given it as milestone-1, 2 but we dont have any such specific order for milestone.
ProjectName | ProjectId | TaskFinishDate | Expected Result - TaskStartDate | Milestone | TaskName |
Project-A | 1 | 25-11-2020 | 25-11-2020 | milestone-1 | B |
Project-A | 1 | 19-01-2024 | 26-11-2020 | milestone-2 | G |
Project-A | 1 | 28-06-2024 | 20-01-2024 | milestone-3 | J |
Project-A | 1 | 30-06-2025 | 29-06-2024 | milestone-4 | E |
Project-A | 1 | 29-08-2025 | 01-07-2024 | milestone-5 | G |
Project-B | 2 | 20-01-2014 | 20-01-2014 | milestone-1 | M |
Project-B | 2 | 15-12-2014 | 21-01-2014 | milestone-2 | U |
Project-B | 2 | 15-12-2014 | 16-12-2014 | milestone-3 | K |
Project-B | 2 | 09-03-2018 | 16-12-2014 | milestone-4 | V |
Project-B | 2 | 16-08-2021 | 10-03-2018 | milestone-5 | A |
Project-C | 3 | 28-09-2021 | 28-09-2021 | milestone-1 | Q |
Project-C | 3 | 29-12-2023 | 29-09-2021 | milestone-2 | F |
Project-C | 3 | 30-04-2025 | 30-12-2024 | milestone-3 | T |
Project-C | 3 | 21-11-2025 | 01-05-2025 | milestone-4 | T |
Project-C | 3 | 28-02-2026 | 22-11-2025 | milestone-5 | Q |
Project-C | 3 | 28-02-2026 | 01-03-2026 | milestone-6 | I |
Project-C | 3 | 21-07-2026 | 01-03-2026 | milestone-7 | E |
Project-C | 3 | 30-08-2026 | 22-07-2026 | milestone-8 | H |
Project-C | 3 | 31-08-2026 | 31-08-2026 | milestone-9 | O |
Project-C | 3 | 31-10-2026 | 01-09-2026 | milestone-10 | I |
Project-C | 3 | 31-01-2027 | 01-11-2026 | milestone-11 | F |
Project-C | 3 | 31-03-2027 | 01-02-2027 | milestone-12 | Y |
Project-C | 3 | 30-06-2027 | 01-04-2027 | milestone-13 | L |
@Anonymous
Add the following column:
StartDate =
VAR __FirstDate =
CALCULATE (
MIN ( Table04[TaskFinishDate] ),
ALLEXCEPT ( table04, Table04[ProjectName] )
)
VAR __OffSetDate =
MINX (
OFFSET (
-1,
ALLSELECTED ( Table04[ProjectName], Table04[TaskName], Table04[TaskFinishDate] ),
ORDERBY ( Table04[TaskFinishDate], ASC, Table04[TaskName], DESC ),
PARTITIONBY ( Table04[ProjectName] )
),
Table04[TaskFinishDate]
)
VAR __Result =
IF ( Table04[TaskFinishDate] = __FirstDate, __FirstDate, __OffSetDate + 1 )
RETURN
__Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
PARTITIONBY ( Table04[ProjectName] )
I'm getting error on this line for the dax shared above
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |