Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
patri0t82
Post Patron
Post Patron

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

 

1 ACCEPTED SOLUTION
slorin
Super User
Super 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

View solution in original post

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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
Super User
Super 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

You did it! That's amazing. Thanks very much.

slorin
Super User
Super User

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

 

Thank you so much for the reply. Is this how you intended to apply the code:

patri0t82_0-1686843326106.png

 

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.

 

patri0t82_1-1686843396341.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors