cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yoshimitsu411 Helper I
Helper I

Sum of Columns up to Current Month

Hi All

 

I have a table like this where the EAC columns are of Whole Number data type.

 

IDJAN EACFEB EACMAR EACAPR EACMAY EACJUN EACJUL EACAUG EACSEP EACOCT EACNOV EACDEC EAC
AB1   2,4929,1399,87612,0181,0364,372-24  
AB22,278           
AB3       10,4167,81852,804  
AB4            
AB5            
AB6            
AB7            
AB8-3,520-280          
AB95,0275,3544,1785,9895,4944,2085,481-79,65167,35,5075,2305,230
             

 

I need to create a new column which displays the Sum of all columns starting from JAN EAC upto the current month. For example, we are in November, so the new column should contain the sum of all values starting from JAN EAC up to NOV EAC for each ID.

 

Thank you for any help.

 

Yoshi

3 REPLIES 3
mcybulski Solution Specialist
Solution Specialist

Re: Sum of Columns up to Current Month

Before creating the column, the code below selects all columnames <= the current date. It then feeds this information to the YTD column.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVFbCsNACLxLvifga1f3s71GyP2v0eiW0EJLGxBGdJxB3bblducFyxkCG3LgAOsoDO8HsoA4MgFpFgzqSVzFnsM7Uk1KRDxeVT/HHNBvfSYYp5UjyroJgt7d7LfLyW0XuP0C1y9wc41V0YTqdEF/TeUjGki8UJvV/blu3DBi9m3MutCsW+RnVx/ouTp3h1aj0RQSpRP3/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"JAN EAC" = _t, #"FEB EAC" = _t, #"MAR EAC" = _t, #"APR EAC" = _t, #"MAY EAC" = _t, #"JUN EAC" = _t, #"JUL EAC" = _t, #"AUG EAC" = _t, #"SEP EAC" = _t, #"OCT EAC" = _t, #"NOV EAC" = _t, #"DEC EAC" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"JAN EAC", Int64.Type}, {"FEB EAC", Int64.Type}, {"MAR EAC", Int64.Type}, {"APR EAC", Int64.Type}, {"MAY EAC", Int64.Type}, {"JUN EAC", Int64.Type}, {"JUL EAC", Int64.Type}, {"AUG EAC", Int64.Type}, {"SEP EAC", Int64.Type}, {"OCT EAC", Int64.Type}, {"NOV EAC", Int64.Type}, {"DEC EAC", Int64.Type}}),
    SumColumns = List.Buffer( List.Transform(
List.Select(List.Transform(Table.ColumnNames(#"Changed Type"), each try Date.From(Text.Start(_,3)  & " 1" ) otherwise null ), each _ <> null and _ < Date.From(DateTime.LocalNow())),
each Text.Upper(Date.ToText(_, "MMM"))& " EAC") ),
    AddYTD = Table.AddColumn(#"Changed Type", "YTD", each List.Sum(Record.ToList(Record.SelectFields(_,SumColumns))))
in
    AddYTD

 

 

 

Yoshimitsu411 Helper I
Helper I

Re: Sum of Columns up to Current Month

Thanks for the quick reply.

 

So with my limited knowledge of Power Query and DAX, am I right in assuming that your code creates a table as well?

 

My table in Power BI exists, so what part of your code do I need to use to add a new column to the existing table?

 

Thanks again.

 

mcybulski Solution Specialist
Solution Specialist

Re: Sum of Columns up to Current Month

Add the two lines below to your table. You will need to change the boldface to the name of your table.

 

SumColumns = List.Buffer( List.Transform(
List.Select(List.Transform(Table.ColumnNames(YourTableName), each try Date.From(Text.Start(_,3) & " 1" ) otherwise null ), each _ <> null and _ < Date.From(DateTime.LocalNow())),
each Text.Upper(Date.ToText(_, "MMM"))& " EAC") ),
AddYTD = Table.AddColumn(YourTableName, "YTD", each List.Sum(Record.ToList(Record.SelectFields(_,SumColumns))))
in
AddYTD

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors