Change the first value of a column based on another column

[Kolumam]

I want to convert the first value of the column to another value based on a column.

Eg. The first value of Start Date of Contract is 1st April 2019. I want to convert this value to the start of the year i.e 1st Jan 2019 based on the column "Name". So every time if the Name column has another value, the first row of the Start Date should start from the start of the year.

Expected Output:

Start Date of Contract	End Date of Contract	Comprehensive O&M Price	Name
1 Jan 2019	31 March 2020	376	Kellogg

@amitchandak @parry2k @harshnathani @az38 @mahoneypat @ryan_mayu @nandic @jdbuchanan71 @Anonymous @v-juanli-msft 

[v-xicai · Accepted solution]

Hi @Kolumam ,

You may create measure like DAX below.

Start Date of Contact_New = VAR _MinDate= CALCULATE(MIN('Table'[Start Date of Contact]), ALLEXCEPT('Table', 'Table'[Name]))

RETURN

IF(COUNTROWS(FILTER('Table','Table'[Name]=MAX('Table'[Name])))>0 , IF( MAX('Table'[Start Date of Contact])=_MinDate, DATE(YEAR(_MinDate), 1,1), MAX('Table'[Start Date of Contact])),BLANK())

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Kolumam , what you want to do with 12th May .

All start date to be 1st Jan

New Date = date(Year([start date]),1,1)

only first april

if([start date] = date(Year([start date]),2,1),date(Year([start date]),1,1),[start date] )

Or min Date

if([start date] = min([Start Date]),date(Year([start date]),1,1),[start date] )

[Kolumam]

@amitchandak 

12th May should remain as 12th May. It shouldn't convert everything. 

Also, please use code editor to put your formulas as it looks messy.

[amitchandak]

@Kolumam , In context of you other post I have added one more formula to compare it with Min date. 

You can also try with Minx if needed

if([start date] = minx(Table,[start date]),date(Year([start date]),1,1),[start date] )

all are new columns 

[Kolumam]

Hi @amitchandak 

The formula is fine but it is still not based on the column "Name". If we don't add this other column, it will convert for just one record.

[Kolumam]

@amitchandak Can you help here please?