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
Solved! Go to 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
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
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
105 | |
77 | |
72 | |
48 | |
47 |
User | Count |
---|---|
158 | |
86 | |
80 | |
68 | |
66 |