cancel
Showing results for
Did you mean:
Regular Visitor

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?

Best regards,

Ugur Gulluev

1 ACCEPTED SOLUTION

Accepted Solutions
Member

Re: Calculating cost per day

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 2
Super User

Re: Calculating cost per day

Hi @ugurgulluev

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

Member

Re: Calculating cost per day

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.

Announcements

Power BI Super User, Greg Deckler, explains

Power Platform Summit North America

Register by September 5 to save \$200

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 79 members 1,534 guests
Recent signins: