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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yoshimitsu411
Resolver I
Resolver 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
Anonymous
Not applicable

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

 

 

 

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.

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors