Sum by date and category

[Anonymous]

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! 🙂

[slorin]

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

[ronrsnfld]

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