Year to Date SUM

[patri0t82]

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]
    )
)

[Syndicate_Admin]

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.

[slorin · Accepted solution]

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

[patri0t82]

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