Forum Discussion
Hayleysea
Resolver II
Resolver IINew 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...
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)
HayleyseaResolver II
Resolver III 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
Super User
Super User@Hayleysea, I suggested a Measure