Cost per month over duration and total cost

D

Dico

Member
Joined
May 23, 2020
Messages
41
Reaction score
0
Points
6
Excel Version(s)
2019 Pro Plus
Hello,

I regularly visit your site, especially the Power Query section which I find great.
I was interested in the last thread of mdowling73 and would like to know how to add the column "Total Cost" in the result of @pinarello (#8)

I put an image to show you the desired result

Thank you in advance for your help.
 

Attachments

  • Total Cost.png
    Total Cost.png
    54.3 KB · Views: 18
Just don't remove the totals column in the query and add it to the grouping step:

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])) + 1), 3)),
    List_of_days = Table.AddColumn(Cost_per_day, "Custom.2", each List.Dates([Start], (Number.From([Finish]) - Number.From([Start]) + 1),#duration(1, 0, 0, 0))),
    Expand_list_of_days = Table.ExpandListColumn(List_of_days, "Custom.2"),
    Remove_Cols = Table.RemoveColumns(Expand_list_of_days,[COLOR=#ff0000]{"Start", "Finish"}[/COLOR]),
    #"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", [COLOR=#ff0000]"Total Cost"[/COLOR]}, {{"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
 

Attachments

  • PQ - trying to calc cost per month over duration (PQ) AliGW.xlsx
    31.7 KB · Views: 13
Hello AliGW :smile:
Thank you for your prompt response,
Indeed, I hadn't thought about it at the Group step,
I was looking for complicated
Thank you and have a nice day
 
Last edited:
Hi,

here now #8 with Total Cost
 

Attachments

  • xlguru - trying to calc cost per month over duration (PQ).xlsx
    31.9 KB · Views: 18
Hi pinarello,
Thank you for the 2nd example,
The solution was indeed in the "Removed Other Columns" step.

I just saw that you are from Germany and I am from France a few kilometers from the German border :)

Have a nice day
 
Hello, Dico,

yes, you're right, I didn't have to do anything else to realize your wish.

I (still) work for a very large, internationally active, German group and in the course of this I have also very often visited colleagues in France. No matter in which country, the most distant ones were China and Mexico, everywhere I met only very nice people.

Privately, the most beautiful moments I experienced in France, when I was riding in a group, on a racing bike through France. Because then the French stop at the roadside and cheer you on. P.S.: My current, carbon, road bike is a french one and comes from Look.

In this sense I wish you and all the others a beautiful and healthy day.
 
Last edited:
Thank you pinarello for this feedback, it was a pleasure to read you.

Normally, I have a good level on Excel and power query but probably the tiredness of the last few weeks made me not see the obvious solution of the 1st file.

Otherwise, I also work (still) for a very large internationnal German group and sometimes move to the head office in Germany.
We probably work for the same group.:smile:

Guten Sonntag zu sammen:)
 
You must log in or register to reply here.
Back
Top