Calculate cost based on date range and percentage allocation

[narangaman]

Hi , I am trying to calculate montly cost spend for a Project (with some employees) from a Program level timesheet data. I have two tables

• one with timesheet entered each day for the last couple of years by every employee in the program (in hours) each day
• Other with employee day rate (per day (8 hour day)) between start date and end date and their percentage allocation between those start and end dates.

Table 1 (program level data) Ex

Employee Username	Billed Hours	Date
A	8	4-jan-23
B	6	4-jan-23
C	4	6-jan-23
A	5	5-jan-23
B	4	5-jan-23
D	2	4-jan-23
E	4	7-jan-24
F	1	8-jan-24
A	1	9-jan-24
B	6	7-jan-24

Table 2 (project level data)

Employee Username	start date	end date	day rate	percentage allocation
A	1-jan-23	5-jun-23	100	50%
A	6-jun-23	31-dec-23	200	100%
A	1-jan-24	4-mar-24	300	60%
B	1-jan-23	5-jun-23	100	100%
B	6-jun-23	31-dec-23	200	100%
C	6-jun-23	31-dec-23	400	20%
C	1-jan-24	4-mar-24	500	50%

I want to create views where i can show cost per month ( for project level ( only A,B and C) employees) 

[Ashish_Mathur · Accepted solution]

Hi,

Here's one way.  In Table 1, write this calculated column formula to bring over the per day rate from Table 2

Per day rate = calculate(sum('Table 2'[day rate]),fiter('Table 2','Table 2'[Employee username]=earlier('Table 1'[Employee Username])&&'Table 2'[Start date]<=earlier('Table 1'[Date])&&'Table 2'[End date]<=earlier('Table 1'[Date])))

Similarly, bring over the percentage allocation column as well.  Effectively, Table 1 is the only relevant table now.  Next, build a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (many to one and single) from the Date column of Table 1 to the Date column of the Calendar Table.  To your visuals/filters/slicers, drag any date dimension from the Calendar Table.

Hope this helps.