Forum Discussion
StephenK
Resolver I
Resolver IRepeat Users within Rolling 12 Month Period
Hey guys, I need some help coming up with a calculated column. I have two tables... a calendar table (has contiguous dates): Date Exercise 1/1/2020 1/2/2020 ...
try this calculated column formula
IsRepeat=VAR _id=FactTable[PersonID] VAR _tbl=FILTER(FactTable,FactTable[PersonID]=_id) VAR _max=MAXX(_tbl,FactTable[Date]) VAR _2ndMax=MAXX(FILTER(_tbl,FactTable[Date]<_max),FactTable[Date]) RETURN IF(_max<=EDATE(_2ndMax,12),1,0)
wdx223_Daniel
Community Champion
Community Championtry this calculated column formula
IsRepeat=VAR _id=FactTable[PersonID] VAR _tbl=FILTER(FactTable,FactTable[PersonID]=_id) VAR _max=MAXX(_tbl,FactTable[Date]) VAR _2ndMax=MAXX(FILTER(_tbl,FactTable[Date]<_max),FactTable[Date]) RETURN IF(_max<=EDATE(_2ndMax,12),1,0)