How to create a Custom Column or New Table based with multiple Column condition

[fa5fou5]

Hi,

Suppose I am having such Table,

How Can I Create a custom column called <Actual Milestone> that calculate the actual milestone based on the earliest date with is in this case 16-Apr-19

Project	Milestone	Completed On	Actual Milestone
59910	Milestone 1	6-Jan-19	Milestone 4
59910	Milestone 2	1-Feb-19	Milestone 4
59910	Milestone 3	14-March-19	Milestone 4
59910	Milestone 4	16-Apr-19	Milestone 4
59910	Milestone 5		Milestone 4

Or Even Better, 

To create a new Table that shows only 

Project	Completed On	Actual Milestone
59910	16-Apr-19	Milestone 4

Thanks a lot

[Anonymous]

Hi buddy,

Try create a table on DAX like i do.

1 - Create a table with this DAX: 

Table = SUMMARIZE(Table1;Table1[Project];"Actual Milestone";LOOKUPVALUE(Table1[Milestone];Table1[Completed On];LASTDATE(Table1[Completed On])))

This will bring to you 2 columns, "Project" and "Actual Milestone", the second column alreasy is refering to the last date of "Table1[Completed On]", so is dynamic.

2- Create a calculated column with this: 

Completed On = LASTDATE(Table1[Completed On])

this should do the trick.

Any questions, ask 😉

[fa5fou5]

Thank you for your reply,

but I get this error message

'A table of multiple values was supplied where a single value was expected.'

I think because I have a column that contain more than on project, In fact the final table would be like

Project 1  --- Milestone 2  --- Date1

Project2 --- Milestone 1 --- Date2

Project3 --- Milestone 3 --- Date3

[Anonymous]

Try this next one:

Table = SUMMARIZE(Table1;Table1[Project];"Actual Milestone";LOOKUPVALUE(Table1[Milestone];Table1[Completed On];LASTDATE(Table1[Completed On]));"Date";LOOKUPVALUE(Table1[Completed On];Table1[Completed On];LASTDATE(Table1[Completed On])))

this one is creating a table who can bring you all info in one.

Be sure to hit the "create table" button on the modeling to use this DAX, just like this.

Just to clarify, i'm using the SUMMARIZE to group the project, the milestone and the last date.

[fa5fou5]

Thank you,

In fact,

when I Have this table of more than 3000 rows, this is a specimen,

59910	CMT	1-May-19
59910	TSVR1	16-May-19
59910	TSVR2	30-May-19
59910	CB	6-Jun-19
59910	HIP	10-May-19
59874	CMT	10-May-19
59874	TSVR1	7-May-19
59874	CSRB	10-May-19
59874	TSVR2	21-May-19
59874	CB	28-May-19
59874	HIP	5-Jun-19

With this code :

Table = SUMMARIZE(Table2,Table2[PCR],"Actual Milestone",LOOKUPVALUE(Table2[HIP],Table2[DATE],LASTDATE(Table2[DATE])),"Date",LOOKUPVALUE(Table2[DATE],Table2[DATE],LASTDATE(Table2[DATE])))

I am getting this result :

Actual Milestone      Date                          PCR

HIP                                    6/5/2019               59874

CB                                      6/6/2019               59910

The Problem as soon as I add rows  (Check I added another row in the end)

59910	CMT	1-May-19
59910	TSVR1	16-May-19
59910	TSVR2	30-May-19
59910	CB	6-Jun-19
59910	HIP	10-May-19
59874	CMT	10-May-19
59874	TSVR1	7-May-19
59874	CSRB	10-May-19
59874	TSVR2	21-May-19
59874	CB	28-May-19
59874	HIP	5-Jun-19
54786	TSVR	5-Jun-19

I receive this message:

A table of multiple vales was supplied where a single value was expected 😞

[Anonymous · Accepted solution]

Hi buddy,

well, this is happening because you have one date being repeted, so you need something like "index column".

I create one column called "KEY" it would serve to resolve your problem, here goes the steps.

i recreate the table, now only using the summerize for column "Project":

Table = SUMMARIZE(Table1;Table1[Project])

 

Create those 2 columns:

last date = CALCULATE(LASTDATE(Table1[Date]);FILTER(Table1;Table1[Project]='Table'[Project]))

Key = CONCATENATE('Table'[Project];'Table'[last date])

 

Create on the original table this column to be referenced:

Key = CONCATENATE(Table1[Project];Table1[Date])

Those "key" columns you can "hide in the report view" so do impact happens.

Now you can access the Name column, like this:

Column = LOOKUPVALUE(Table1[Name];Table1[Key];'Table'[Key])

This should do the trick,

Also, sorry my bad english, it's not my native language.

Any questions, ask 😉