Forum Discussion
Post PatronYear to Date SUM
Hi!
I'm using Power Query and I need help creating a new column. I have a column called [Month / Year], which is a date column. It contains one row per month. Each row looks like this "1/1/2021, 2/1/2021, 3/1/2021, etc." I have another column called [Energy - Total]. It contains a decimal number. These decimal numbers are the total sum of hours for each month. For each row, I need to find the YEAR TO DATE sum.
So far the code I'm using below is providing a rolling SUM of all the [Energy - Total] column, but it's not resetting each year.
List.Sum(
List.FirstN(
#"Renamed Columns1"[#"Energy - Total"],
[Index]
)
)
Add [#"Month / Year"]<=Date
(Table,Date) =>
List.Sum(Table.SelectRows(Table, each Date.Year([#"Month / Year"])=Date.Year(Date) and [#"Month / Year"]<=Date)[#"Energy - Total"])Stéphane
5 Replies
slorinSuper User
Add [#"Month / Year"]<=Date
(Table,Date) =>
List.Sum(Table.SelectRows(Table, each Date.Year([#"Month / Year"])=Date.Year(Date) and [#"Month / Year"]<=Date)[#"Energy - Total"])Stéphane
patri0t82You did it! That's amazing. Thanks very much.
- slorin
Hi
...
Previous_Step = ...
Function_YTD = (Table,Date) =>
List.Sum(Table.SelectRows(Table, each Date.Year([#"Month / Year"])=Date.Year(Date))[#"Energy - Total"]),
YTD = Table.AddColumn(Previous_Step, "YTD", each Function_YTD(Previous_Step,[#"Month / Year"]))
...Stéphane
- patri0t82
Thank you so much for the reply. Is this how you intended to apply the code:
Unfortunately, while it's not providing an error, it's returning the same value for each row and not the YTD sum as of each month.
Syndicate_AdminAdministrator
Hello, Stéphane! How can I modify the formula to add other column as reference?
I have to Sum YTD and by Location and can't figure out a way to add the second condition.