Forum Discussion

patri0t82's avatar
patri0t82
Post Patron
3 years ago
Solved

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...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    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

slorin's avatar
slorin
Super User
3 years ago

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's avatar
patri0t82
Post Patron
3 years ago

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.