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 ...
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.
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.