DAX Calculated Column

Hi  Anonymous  ,

Here are the steps you can follow：

1. Enter the power query through Transform data, select Advanced Editor, copy and paste, and done.

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoysyJKTUwMDI3MjAyVNJRMjQyBpImBgYGMCpWJ1rJN7ESiyojU4gyIwOYOq/SvFRsxkEVGiMpzMFmohlEnSlMmWNpOhZVhqZgVYZGUFXBqQXYVBlBDDOEWBoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month'Year" = _t, ID = _t, #"Forecasted Gross" = _t, #"Forecasted Net" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month'Year", type text}, {"ID", Int64.Type}, {"Forecasted Gross", Int64.Type}, {"Forecasted Net", Int64.Type}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Forecasted Gross", "Forecasted Net"}, "Attribute", "Value"),

    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"ID", "Forecasted Net", "Attribute", "Value"}, "Attribute.1", "Value.1"),

    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Unpivoted Columns1", {"ID", "Attribute", "Value", "Attribute.1", "Value.1"}, "Attribute.2", "Value.2"),

    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns2", "Custom", each [Value]),

    #"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Value", "Attribute.1"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),

    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Custom", "Attribute.2"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged.1"),

    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1", List.Sum),

    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Merged.1]), "Merged.1", "Value.2", List.Sum),

    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column1",{"Attribute"}),

    #"Filled Down" = Table.FillDown(#"Removed Columns",{"April'2021#(tab)Forecasted Gross"}),

    #"Filled Up" = Table.FillUp(#"Filled Down",{"April'2021#(tab)Forecasted Gross", "May'2021#(tab)Forecasted Gross"}),

    #"Filled Down1" = Table.FillDown(#"Filled Up",{"May'2021#(tab)Forecasted Gross", "June'2021#(tab)Forecasted Gross"}),

    #"Filled Up1" = Table.FillUp(#"Filled Down1",{"June'2021#(tab)Forecasted Gross"}),

    #"Filled Down2" = Table.FillDown(#"Filled Up1",{"July'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Net"}),

    #"Filled Up2" = Table.FillUp(#"Filled Down2",{"July'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Net"}),

    #"Removed Duplicates" = Table.Distinct(#"Filled Up2"),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"ID", "April'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Gross", "May'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Gross", "June'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Gross", "July'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Net"})

in

    #"Reordered Columns"

2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.