I have an interesting challenge that I’m sure there’s a simple solution for, but I haven’t been able to solve it. I have a table from an Azure SQL database which tracks my team’s individual annual goals for billable hours. Different people have different goal amounts, and to further complicate it the goals might change over time. For example, a new hire might have a goal of 5 billable hours per week for their first month, 10 their second month, 15 their third month, etc. The fact table looks as follows:
In this sample we’ve got 3 different employees and 8 different records. What I need to do is create a measure that allows me to aggregate the total goal hours by employee, by date. I can easily add a column to the fact table that converts the annual goal to a daily goal ::: Daily Goal Hours = DIVIDE( [Annual Goal Hours], 260, 0), and I can use the Employee ID field to join this fact table to my Employee lookup table to allow me to slice and filter by department, supervisor, etc.
I assume I need to use some version of SUMX to iterate through the Daily Goal Hours by each date and filter to only the dates in range, multiplying the Daily Goal Hours by the number of Weekdays in that range. To use the example above, the aggregate Q1 2020 goal for these three employees would be 887 Hours.
What I haven’t been able to figure out is how to relate the list of dates between From Date and To Date to the daily goal. I think I need to create a table or virtual table with columns for Employee, Date, and Daily Goal, where all of the dates between “From Date” and “To Date” are unpivoted, and weekends are filtered out, but I’m stuck on how to do that. Or maybe there’s a more efficient way to reference a date value between two date columns and lookup a value in that row?