Determine Which Employees Have Worked 13 Weeks Consecutively

[SimonNU]

Hi guys

I'm at a bit of a loss as to how to calculate this. Usually I can give it a good shot but this is quite a weird one...

I have the below model:

Fact Table:
Employee | Date | Hours

Date Table:
Date | YYYYWW

The question: How would I determine which employees have worked 13 weeks consecutively and which have not? Ideally this would be a calculated column but if it's possible to do in a measure then that's fine too!


Thanks!

 

[Matt Allington]

How about doing a distinct count over a filter of the last 13 weeks. If the answer is 13, then they worked all 13.
You should add an ID column to your date table.

Something like this.

=if(calculate(distinctcount(employee), filter(all(date table), datetable[id] >= min(datetable[id])-12 && datetable[id] <= max(datetable[id])))=13,"true","false")

you our can read about ID columns here. Power Pivot Calendar Tables -

 

[SimonNU]

Thanks Matt.

The 13 weeks would relate to the current filter though, wouldn't it? Maybe there's something i'm not understanding but it looks like I would have to manually iterate through every week and note down which employees satisfied the criteria. Unfortunately this is unfeasible because my data spans several years and thousands of people.

 

[Matt Allington]

I can't see any way to solve this problem without iterating over every week. By definition that is required - I think!