Forum Discussion
Helper ICalculate No Payment Last 2 rolling weeks
There is probably a better formula for this but this should work
Trial1 = IF(ISBLANK( CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())))
,2,
MOD(CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())),2))
Resolver IVHi Greg,
Could you clarify.
You stated "I need to return either ‘0’ indicating paid both weeks, ‘1’ indicating paid one week, or ‘2’ indicating not paid last two weeks."
Then below in the example stated
"Bill would return '1', James would return '0' and Susan would return '2'"
which contradicts the first statement.
If you require 2 to indicate paid in the last two weeks and zero for not paid in the last two weeks CountRows might be the function you require.
Helper IHi MarkS,
Sorry for the confusion. I had James and Susan mixed up. James would return '2', since he's not worked last two weeks, and Susan would return '0', since she has worked the last two weeks.