Getting a date value from a text column

[MartinC]

Hi All,

I am using data from Primavera P6, copied into Excel, and then used in Power BI.  My portfolio of projects is approximately 300 different projects.  Each one has 10 of the same milestones.  I have added a new column that identifies each of the milestones, now I need to have the dates for each one.

Each activity is it's own row.  In the row, I have the activity ID, Start, Finish, and the identifier for the milestone.  What I want is when "Milestone X = 1" for Power BI to return the date from the "Finish" column.

To identify the milestone type I have: 

To get the date I have tried: 

Any help will be appreciated! Thanks 🙂

[ajohnso2]

Are your dates stored as dates in power query?

If not add or replace your current date column as Date.FromText(<date column>, "dd/mm/yyyy")

Your Dax measure perhaps wont work if you are mixing string with dates.

Try

if('Activities'[Activity Name] = "Project Brief Accepted", 'Activities'[Finish Date], BLANK())

[MartinC]

Thanks for the suggestion, I did try that and just get #ERROR

[Greg_Deckler]

@MartinC Can you provide sample data? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[MartinC]

Hi Greg,

The data is commercially sensitive so I can't share too much sadly 😞

Let's say I have 3 activities (rows) which have columns for "Activity ID", "Activity Name", "Start", "Finish", "Milestone 1", "Milestone 2" and "Milestone 3".

Each row will only have a value in either "Milestone 1" 2 or 3.  The result I need in a new column is the finish date from "Finish" when either Milestone 1, 2 or 3 have a value.

I hope that helps?