New row indicator against specific past date

[Hayleysea]

Hi There,

I need a calculated column that indicates whether an ID is new when comparing to other date with a key indicator as per the example below:

ID	Date	Key Indicator	Result
A	1/03/2020	1	0
B	1/03/2020	1	0
A	2/03/2020		0
B	2/03/2020		0
A	3/03/2020		0
B	3/03/2020		0
C	3/03/2020		1

Key indicator is against 1/03/2020 so this is what I want to compare to.

I want to compare the latest day (3/03/2020) with the day that has the key indicator (1/03/2020) and produce a 1 in the result column if it is in fact a new ID that's been created since the last date with the key indicator, otherwise a 0.

Any help greatly appreciated!!

[Hayleysea · Accepted solution]

I figured it out using some of what @amitchandak said and making some changes.

Ended up with a calculated column like this:

Calculated Column=

var keyindicatordate = CALCULATE(MAX(table[Date]), FILTER(table, table[keyindicator]="1"))

var maxdate= CALCULATE(MAX(table[Date]), FILTER(table, Table[ID]=EARLIER(table[ID])))

return IF(CALCULATE(DISTINCTCOUNT(table[ID]), FILTER(table, table[Date]=keyindicatordate && table[ID]=EARLIER(table[ID])))-CALCULATE(DISTINCTCOUNT(table[ID]), FILTER(table, table[Date]=maxdate && table[ID]=EARLIER(table[ID])))<0 && table[max week calculated column]=1,1,0)

[amitchandak]

@Hayleysea, I suggested a Measure

[amitchandak]

You will not able to change the date in case you use a calculated column. try like a measure

measure =
var _max = maxx('Table','Date'[Date])
var _min = Minx('Table','Date'[Date])

return
if(CALCULATE(distinctCOUNT('Table'[ID]), FILTER(all('Table'), 'Table'[Date]=_max)) - CALCULATE(distinctCOUNT('Table'[ID]), FILTER(all('Table'), 'Table'[Date]=_min)) >=0,1, 0)