Hello, I have a fact table that includes 10 years of paychecks. I am using it to calculate the number of employees active on each pay date. Employees are paid every two weeks, but the exact date varies due to holidays. I have a related lookup table that has all of the pay dates I am including in the dashboard (used for filtering out non-standard pays) which also includes an index column (so that more recent paychecks have a higher number).

I’d like to create measures that compare the current number of employees vs. the number from 1, 6, and 26 paychecks ago. In plain English, I feel like I should be able to say, “Employees on Previous Paycheck = get the index number of the most recent paycheck, subtract 1, and return the number of employees for the paycheck with that index number.” And then create additional measures subtracting 6, 26, or whatever.

Any help would be much appreciated, I’m sorry if this has been answered in a previous post – I’m new enough that I still have trouble cross-referencing answers that aren’t in the exact terms I’m looking for.

Thanks!