cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ugurgulluev Regular Visitor
Regular Visitor

Calculating cost per day

Hi all,

 

I have two seperate tables with relationship as follows:

 

Employee IDYearMonthCost
120191€ 1000

 

Employee IDDepartmentDate 
1Alfa Corp01/01/2019
1Alfa Corp02/01/2019
1Alfa Corp03/01/2019
1Beta Corp04/01/2019
1Beta Corp05/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 IDYearMonthDepartmentCost
120191Alfa Corp(1000/30)*3
120191Beta 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

1 ACCEPTED SOLUTION

Accepted Solutions
ZunzunUOC Member
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
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
    #"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.

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 79 members 1,534 guests
Please welcome our newest community members: