Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |