Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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)
Solved! Go to 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.
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.