Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sum by date and category

Hi Communtiy 

I have the below dataset (The first 3 variables).  I need to sum the Value by Measure  and by Date every three months starting on September 2020. Any ideas in how I could do that? For example here it is what I need to get 

 

 

Many thanks for your help! 🙂

 

 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    In Power Query (Transform option from BI), you can 

    • Add an "end of quarter" column
    • Group by "end of quarter" and "Measure"
    • Aggregate with Sum of Value
  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    let
    Source = YourSource,
    Index = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    Groupe = Table.Combine(
      Table.Group(
      Index,
      {"Date"},
      {{"Data", each Table.Group(
        _,
        {"Measure"},
        {{"Max_Month",each List.Max([Date])},
         {"Sum",each List.Sum([Value])}})}},
      GroupKind.Local,
      (x,y) => if y[Date]<Date.AddMonths(x[Date],3) then 0 else 1)
     [Data]),
    Join = Table.NestedJoin(Index, {"Date", "Measure"}, Groupe, {"Max_Month", "Measure"}, "Group", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Group", {"Sum"}, {"Sum"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}})
    in
    Sort

     

    Stéphane