I’m looking for guidance on a DAX measure. Our employees are paid every 2 week. The data as it’s captured in the fact table is week 1 data is assigned week 1 and week 2 is assigned week 2. This week 1/week 2 is always repeated. Each week is also assigned a unique week ending date (Sunday of the week) AND a unique time period.
What I need to do, is sum the hours for Week 1 and Week 2 - based on the actual calendar so in this case Week 1 is Week Ending 12/6/20 and Week 2 is Week Ending 12/13/20 AND if the sum of those 2 weeks is over 80, I need to flag it.
I need to be able to ensure this repeats through all of my data, ensuring the addition of week 1 and week 2 is following the logical calendar.
I suspect the Time Period column can assist with this calculation I’m just struggling with how to put this all together.
I’ve included a .pbix file with the sample data as well.
EmpHours.pbix (49.5 KB)