Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
v4anand18
Advocate I
Advocate I

Creating a Custom Column

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 NoAcct Open DateSite IDAcct Opened YearAcct Opened MonthColumn2Column3Column4Column5
6106702936/27/201361060115220136 SIN EDG CONSULTING PTE LTD
6106705876/28/201361067058720136 SIN NINE-V POST
6106732217/17/201361067322120137 SIN LATIN ASIA TRADING (SINGAPORE) PTE LTD
6107253249/17/201461072532420149 SIN SKYWAVES AGENCIES PTE LTD
9529404659/22/201661081870720169 SIN WEST ASIA TRADING & ENGINEERING PTE LTD
9529405229/22/201661081868220169 SIN UNISTO PTE LTD
9529405359/22/201661076592420169 SIN PARAMOUNT BED ASIA PACIFIC PTE. LTD.
9529406059/22/201661081876520169 SIN SC CAPITAL PARTNERS PTE. LTD.
6107446689/3/201561074466820159 SIN ELOHIM BY SABRINA GOH PTE LTD
9529408619/25/201661081892120169 SIN ARIRANG TRADING PTE LTD
9542226661/16/201761082790120171 SIN SKETCH DESIGN CONSULTANTS PTE. LTD.
9529410049/26/201661005455420169 SIN STRAITS MARINE SUPPLY PTE LTD
9679081574/11/201561077842720154 SIN CALIBRE SERVICES & SOLUTIONS PTE. LTD.
9529411459/26/201661081910620169 SIN THE BREADWINNERS PTE. LTD.
9529411749/27/201661081926320169 SIN NEW COSMOS ELECTRIC CO., LTD.
9529417109/27/201661081897620169 SIN INNODIENT-HAUSEN PTE. LTD
9529421199/29/201695294211920169 SIN TWINS OFFICE SUPPLIES P/L
9679124973/28/201661079575220163 SIN CREATIVE POT PTE LTD
6107463543/20/201561074635420153 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 NoAcct Open DateSite IDAcct Opened YearAcct Opened MonthColumn2Column3Column4Column5
9606245202/3/201861000033320182 SIN AMSBACH MARINE (S) PTE. LTD.
9542340838/13/201261000033320128 SIN AMSBACH MARINE (S) PTE LTD
6100003331/31/19886100003331988110036SIN 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 

 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.