Please help - trying to calc cost per month over duration

M

mdowling73

New member
Joined
Nov 3, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
Here is a very simple version of what I am trying to do. I have tried this in power query as well as Power Bi. I have gotten this to work with "list dates", but the data tables get really large in the actual table. Keep in mind, I have several projects running and some of them are over a year long. This would be a continuing table, updated monthly, and projects will roll on and off but I need to see the costs per month vs what I am billing per month - prorated/ averaged is fine for this purpose but I need it to reflect appropriately for partial months.

If list dates is the best way, so be it but I could use help with that too. I am still learning power bi, so go easy on me. I do not know anything about DAX

I think I need to calculate the number of days between the dates, divide the total cost by the duration and get a daily rate so it can be prorated.



I know this may be simple for the data crunchers but I would really appreciate some guidance from an expert. If you don't have time to go into detail, just point me in a direction.

Thank you in advance!!!
 

Attachments

  • example.xlsx
    12.2 KB · Views: 13
  • example.JPG
    example.JPG
    43.4 KB · Views: 8
Here is a solution using the List.Dates method.

M Code:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type datetime}, {"Finish", type datetime}, {"Total Cost", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start", type date}, {"Finish", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Finish]-[Start]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each Value.Divide([Total Cost],[Custom])),
    #"Inserted Rounding" = Table.AddColumn(#"Added Custom1", "Round", each Number.Round([Custom.1], 2), type number),
    #"Added Custom2" = Table.AddColumn(#"Inserted Rounding", "Custom.2", each List.Dates([Start],[Custom],#duration(1, 0, 0, 0))),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Start", "Finish", "Total Cost", "Custom", "Custom.1"}),
    #"Extracted Month Name" = Table.TransformColumns(#"Removed Columns", {{"Custom.2", each Date.MonthName(_), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Month Name",{"Name", "Custom.2", "Round"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Name", "Custom.2"}, {{"Value", each List.Sum([Round]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Custom.2]), "Custom.2", "Value")
in
#"Pivoted Column"
 

Attachments

  • PQ Monthly Breakdown AliGW.xlsx
    21.7 KB · Views: 18
Thank you very much Ali! yours is much cleaner than the mess I had made before. One thing, how would you recommend separating the months from different years? i.e. when January 2021 rolls around, it combines it into the January column. I see where you extracted the month name - would you extract the year the same way?

Thanks!!
 
Hi,

take that modified Query which saves some steps.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type datetime}, {"Finish", type datetime}, {"Total Cost", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start", type date}, {"Finish", type date}}),
    Cost_per_day = Table.AddColumn(#"Changed Type1", "Cost/day", each Number.Round([Total Cost] / (Number.From([Finish]) - Number.From([Start])), 2)),
    #"Added Custom2" = Table.AddColumn(Cost_per_day, "Custom.2", each List.Dates([Start], Number.From([Finish]) - Number.From([Start]),#duration(1, 0, 0, 0))),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Start", "Finish", "Total Cost"}),
    #"MMM/YY" = Table.AddColumn(#"Removed Columns", "Month/Year", each Text.Start(Date.MonthName([Custom.2]), 3) & "/" & Text.End(Text.From(Date.Year([Custom.2])), 2)),
    #"Extracted Month Name" = Table.TransformColumns(#"MMM/YY", {{"Custom.2", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Name", "Month/Year"}, {{"Value", each List.Sum([#"Cost/day"]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Month/Year"]), "Month/Year", "Value")
in
    #"Pivoted Column"
 

Attachments

  • xlguru - trying to calc cost per month over duration (PQ).xlsx
    22.1 KB · Views: 7
Last edited:
One step to much in last post.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type datetime}, {"Finish", type datetime}, {"Total Cost", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start", type date}, {"Finish", type date}}),
    Cost_per_day = Table.AddColumn(#"Changed Type1", "Cost/day", each Number.Round([Total Cost] / (Number.From([Finish]) - Number.From([Start])), 2)),
    #"Added Custom2" = Table.AddColumn(Cost_per_day, "Custom.2", each List.Dates([Start], Number.From([Finish]) - Number.From([Start]),#duration(1, 0, 0, 0))),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Start", "Finish", "Total Cost"}),
    #"MMM/YY" = Table.AddColumn(#"Removed Columns", "Month/Year", each Text.Start(Date.MonthName([Custom.2]), 3) & "/" & Text.End(Text.From(Date.Year([Custom.2])), 2)),
    #"Grouped Rows" = Table.Group(#"MMM/YY", {"Name", "Month/Year"}, {{"Value", each List.Sum([#"Cost/day"]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Month/Year"]), "Month/Year", "Value")
in
    #"Pivoted Column"
 

Attachments

  • xlguru - trying to calc cost per month over duration (PQ).xlsx
    22.1 KB · Views: 9
Sorry,

but all good things are 3!

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Changed_Type = Table.TransformColumnTypes(Source,{{"Start", type date}, {"Finish", type date}}),
    Cost_per_day = Table.AddColumn(Changed_Type, "Cost/day", each Number.Round([Total Cost] / (Number.From([Finish]) - Number.From([Start])), 2)),
    List_of_days = Table.AddColumn(Cost_per_day, "Custom.2", each List.Dates([Start], Number.From([Finish]) - Number.From([Start]),#duration(1, 0, 0, 0))),
    Expand_list_of_days = Table.ExpandListColumn(List_of_days, "Custom.2"),
    Remove_Cols = Table.RemoveColumns(Expand_list_of_days,{"Start", "Finish", "Total Cost"}),
    #"MMM/YY" = Table.AddColumn(Remove_Cols, "Month/Year", each Text.Start(Date.MonthName([Custom.2]), 3) & "/" & Text.End(Text.From(Date.Year([Custom.2])), 2)),
    Grouping_Rows = Table.Group(#"MMM/YY", {"Name", "Month/Year"}, {{"Value", each List.Sum([#"Cost/day"]), type number}}),
    Pivoted_Cols = Table.Pivot(Grouping_Rows, List.Distinct(Grouping_Rows[#"Month/Year"]), "Month/Year", "Value")
in
    Pivoted_Cols
 
I am learning so much. I have put off asking this question way too long - You guys are great!!
 
I think if you have a lot of quite long-running projects, then the query internal breakdown by project days can become a time factor to calculate the exact monthly costs.

So here is the variant where the projects are only broken down by months, but the costs are still calculated exactly by days.

Furthermore I saw that your calculation to determine the project days (Finish - Start) was wrong, because you have to increase this result by 1.
 

Attachments

  • xlguru - trying to calc cost per month over duration (PQ).xlsx
    31.7 KB · Views: 19
This is a great solution. You are correct; several 18 month projects really increase the size of the query. Especially since this is meant to be an ongoing tool.
 
An alternative to:
Code: 
#"MMM/YY" = Table.AddColumn(#"Removed Columns", "Month/Year", each Text.Start(Date.MonthName([Custom.2]), 3) & "/" & Text.End(Text.From(Date.Year([Custom.2])), 2)),
is
Code: 
#"MMM/YY" = Table.AddColumn(#"Removed Columns", "Month/Year", each Date.ToText([Custom.2],"MMM/yy")),
 
You are right, with Date.ToText it is easier.
 
You must log in or register to reply here.
Back
Top