Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All
I have a table like this where the EAC columns are of Whole Number data type.
ID | JAN EAC | FEB EAC | MAR EAC | APR EAC | MAY EAC | JUN EAC | JUL EAC | AUG EAC | SEP EAC | OCT EAC | NOV EAC | DEC EAC |
AB1 | 2,492 | 9,139 | 9,876 | 12,018 | 1,036 | 4,372 | -24 | |||||
AB2 | 2,278 | |||||||||||
AB3 | 10,416 | 7,818 | 52,804 | |||||||||
AB4 | ||||||||||||
AB5 | ||||||||||||
AB6 | ||||||||||||
AB7 | ||||||||||||
AB8 | -3,520 | -280 | ||||||||||
AB9 | 5,027 | 5,354 | 4,178 | 5,989 | 5,494 | 4,208 | 5,481 | -79,65 | 167,3 | 5,507 | 5,230 | 5,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
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.
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