cancel
Showing results 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

Resolver II

## New row indicator against specific past date

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!!

1 ACCEPTED SOLUTION
Resolver II

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)
3 REPLIES 3
Resolver II

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)
Super User

@Hayleysea, I suggested a Measure

Super User

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)``````