Calculating cost per day

[Anonymous]

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

[ZunzunUOC · Accepted solution]

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.

[Mariusz]

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.