Forum Discussion
Calculating cost per day
Hi all,
I have two seperate tables with relationship as follows:
| Employee ID | Year | Month | Cost |
| 1 | 2019 | 1 | € 1000 |
| Employee ID | Department | Date |
| 1 | Alfa Corp | 01/01/2019 |
| 1 | Alfa Corp | 02/01/2019 |
| 1 | Alfa Corp | 03/01/2019 |
| 1 | Beta Corp | 04/01/2019 |
| 1 | Beta Corp | 05/01/2019 |
Using these, I'm trying to create a new table to allocate the cost per resource to departments, based on the days spent in this specific department.
The formula I'd like to use is: (Monthly Cost Per Resource/30)*Number of days spent in department; like the following:
| Employee ID | Year | Month | Department | Cost |
| 1 | 2019 | 1 | Alfa Corp | (1000/30)*3 |
| 1 | 2019 | 1 | Beta Corp | (1000/30)*2 |
However, I couldn't figure out how to do that.
Is there a way you can think of to succeed this?
Thanks for your help in advance.
Best regards,
Ugur Gulluev
I would create a new table with the next code:
Result = SELECTCOLUMNS(CROSSJOIN(Days;Employes);"ID";Employes[Employee ID];"YEAR";YEAR(Days[Date ]);"MONTH";MONTH(Days[Date ]);"DEPARTMENT";Days[Department];"COST";(Employes[Cost]/DAY(EOMONTH(Days[Date ];DAY(Days[Date ])))*COUNTROWS(FILTER(Days;Days[Department]=EARLIER(Days[Department])))))
Best Regards,
Miguel
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
You can use Qery Editor to achieve this, please see the below from your example.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMLQEUiCmoYGBgVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Year = _t, Month = _t, Cost = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Cost", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "YearMonth", each [Year] * 100 + [Month], Int64.Type) in #"Added Custom"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSUtUcM4vKgCyDQz1gcjIwNBSKVYHi7QRfmljDGmn1BKEtAl+aVOEdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Department = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Department", type text}, {"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type), #"Grouped Rows" = Table.Group(#"Added Custom", {"Employee ID", "Department", "YearMonth"}, {{"No of Days", each Table.RowCount(_), type number}}), #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Employee ID", "YearMonth"}, Employee, {"Employee ID", "YearMonth"}, "Employee", JoinKind.LeftOuter), #"Expanded Employee" = Table.ExpandTableColumn(#"Merged Queries", "Employee", {"Year", "Month", "Cost"}, {"Year", "Month", "m.Cost"}), #"Added Custom1" = Table.AddColumn(#"Expanded Employee", "Cost", each ( [m.Cost] / 30 ) * [No of Days], type number), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Employee ID", "Year", "Month", "Department", "Cost"}) in #"Removed Other Columns"Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I would create a new table with the next code:
Result = SELECTCOLUMNS(CROSSJOIN(Days;Employes);"ID";Employes[Employee ID];"YEAR";YEAR(Days[Date ]);"MONTH";MONTH(Days[Date ]);"DEPARTMENT";Days[Department];"COST";(Employes[Cost]/DAY(EOMONTH(Days[Date ];DAY(Days[Date ])))*COUNTROWS(FILTER(Days;Days[Department]=EARLIER(Days[Department])))))
Best Regards,
Miguel
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
