Creating a Custom Column

[v4anand18]

Hi All,

I am trying to create a custom column for one of my report using DAX but i am stuck. Please share your inputs. 

My data looks like this

Account No	Acct Open Date	Site ID	Acct Opened Year	Acct Opened Month	Column2	Column3	Column4	Column5
610670293	6/27/2013	610601152	2013	6		SIN		EDG CONSULTING PTE LTD
610670587	6/28/2013	610670587	2013	6		SIN		NINE-V POST
610673221	7/17/2013	610673221	2013	7		SIN		LATIN ASIA TRADING (SINGAPORE) PTE LTD
610725324	9/17/2014	610725324	2014	9		SIN		SKYWAVES AGENCIES PTE LTD
952940465	9/22/2016	610818707	2016	9		SIN		WEST ASIA TRADING & ENGINEERING PTE LTD
952940522	9/22/2016	610818682	2016	9		SIN		UNISTO PTE LTD
952940535	9/22/2016	610765924	2016	9		SIN		PARAMOUNT BED ASIA PACIFIC PTE. LTD.
952940605	9/22/2016	610818765	2016	9		SIN		SC CAPITAL PARTNERS PTE. LTD.
610744668	9/3/2015	610744668	2015	9		SIN		ELOHIM BY SABRINA GOH PTE LTD
952940861	9/25/2016	610818921	2016	9		SIN		ARIRANG TRADING PTE LTD
954222666	1/16/2017	610827901	2017	1		SIN		SKETCH DESIGN CONSULTANTS PTE. LTD.
952941004	9/26/2016	610054554	2016	9		SIN		STRAITS MARINE SUPPLY PTE LTD
967908157	4/11/2015	610778427	2015	4		SIN		CALIBRE SERVICES & SOLUTIONS PTE. LTD.
952941145	9/26/2016	610819106	2016	9		SIN		THE BREADWINNERS PTE. LTD.
952941174	9/27/2016	610819263	2016	9		SIN		NEW COSMOS ELECTRIC CO., LTD.
952941710	9/27/2016	610818976	2016	9		SIN		INNODIENT-HAUSEN PTE. LTD
952942119	9/29/2016	952942119	2016	9		SIN		TWINS OFFICE SUPPLIES P/L
967912497	3/28/2016	610795752	2016	3		SIN		CREATIVE POT PTE LTD
610746354	3/20/2015	610746354	2015	3		SIN		DECO-BASE ENTERPRISE PTE LTD

I am trying to create a column Open Date based on Site ID. Each Site ID has been repeated multiple times in the data but i want to retreive the date from "Acct Open Date" column that has the earliest date. 

For eg. Consider Site ID 610000333

Account No	Acct Open Date	Site ID	Acct Opened Year	Acct Opened Month	Column2	Column3	Column4	Column5
960624520	2/3/2018	610000333	2018	2		SIN		AMSBACH MARINE (S) PTE. LTD.
954234083	8/13/2012	610000333	2012	8		SIN		AMSBACH MARINE (S) PTE LTD
610000333	1/31/1988	610000333	1988	1	10036	SIN		AMSBACH MARINE (S) P/L

So in this case, i should get the "EARLIEST DATE" i.e. 1/31/1988 in the new column. So in the new custom column, wherever i have Site ID as "610000333", it should reflect as "1/31/1988". 

Please share your inputs. 

Thanks 

Vivek.S 

[Anonymous · Accepted solution]

HI @v4anand18,

You can use below formula to get earliest date based on current group site id:

Earliest Date =
MINX (
    FILTER ( ALL ( Table ), [Site ID] = EARLIER ( [Site ID] ) ),
    [Acct Open Date]
)

Regards,
Xiaoxin Sheng