How to calculate fist occurrence

[Sanaz]

Hi all

I need your help/advice on how to calculate "Result-first export date" from table below.

Basically, For each ID on each row, it should first check through column "Export/Import" and find "EXP" and then return the minimum export date from "Date" column in the result column. Say for example: For ID= A12, first should find where is the EXP located under "Export/Import", it's on row#3&8, so the minimum Export date is on row#8, now it should fill in 3/2/2018 for all the rows that are related to ID=A12.

#	ID	Export/Import	Date	Result- First export date
1	C11	IMP	1/1/2018	5/2/2018
2	B12	IMP	2/1/2019	2/1/2019
3	A12	EXP	4/10/2022	3/2/2018
4	A12	IMP	1/1/2018	3/2/2018
5	B12	EXP	2/1/2019	2/1/2019
6	A12	IMP	2/1/2018	3/2/2018
7	C11	EXP	5/2/2018	5/2/2018
8	A12	EXP	3/2/2018	3/2/2018
9	A12	IMP	3/11/2018	3/2/2018

I kindly appreciate your time and effort here.

Thanks in advance,

Sanaz

[v-jayw-msft · Accepted solution]

Hi @Sanaz ,

Check the formula.

Column = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Export/Import]="EXP"))

[pbi-novice]

Hi Sanaz,

You can make a calculated table and relate it to this table by "ID", see screenshot below.
DAX for calculated table ("Table" is original table, "MinDate" is calculated table):

MinDate = 

GROUPBY(

FILTER(

'Table',

'Table'[Export/Import] = "EXP"

),

'Table'[ID],

'Table'[Export/Import],

"FirstExpDate" ,

MINX(

CURRENTGROUP(),

[Date]
)

)