Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.