Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
70 | |
67 | |
51 | |
39 | |
26 |
User | Count |
---|---|
87 | |
55 | |
45 | |
42 | |
36 |