Forum Discussion

619SK's avatar
619SK
Helper II
2 years ago
Solved

Week on Week in Column

I want to calculate WoW% in column field  and not as measure which post in values.

So ideally i am doing wow%  by adding column

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi 619SK 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKLcvMLy1WCE9NzVaK1SFXxLm0qCg1r2TABXwSi4nhxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _,type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WoW% ", each (List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Current Week")[Count])-List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Last Week")[Count]))/List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Last Week")[Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Week"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Week"}, {"Week"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Week", "Count", "WoW% "})
in
    #"Reordered Columns"

    Output

    Best Regards!

    Yolo Zhu

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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 619SK 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKLcvMLy1WCE9NzVaK1SFXxLm0qCg1r2TABXwSi4nhxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _,type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WoW% ", each (List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Current Week")[Count])-List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Last Week")[Count]))/List.Min(Table.SelectRows(#"Grouped Rows",(x)=>x[Week]="Last Week")[Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Week"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Week"}, {"Week"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Week", "Count", "WoW% "})
in
    #"Reordered Columns"

    Output

    Best Regards!

    Yolo Zhu

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

     

    • PijushRoy's avatar
      PijushRoy
      Community Champion
      2 years ago

      Hi 619SK 

      Can you please try below measure, please replace the column name in DAX 

      Previous Week = Calculate(Count(table_name),Column_Name="Previous Week"))
Current Week = Calculate(Count(table_name),Column_Name="Current Week"))
Last Week = Calculate(Count(table_name),Column_Name="Last Week"))

Last Result = Divide((Current week - last week),Last Week)