Calculate the sum of Value for the last 365 days - with Power Query

0
votes

This is how my table looks like in Power Query:

enter image description here

For each combination of date and Customer No (No), I need to calculate the sum of Value for the last 365 days.

I need this in Power Query, I know I could do it with DAX.

1
powerbipowerquery
Can you show how your output table should look like? – balaji
Try this tutorial - excelguru.ca/blog/2015/03/31/… this will obviously give you some direction. – mkRabbani

1 Answers

0
votes

Are you looking for a 365 day total as of each and every date in the table? If so, then this seems to work

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No", Int64.Type}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type" ,"RunningTotal",(i)=>List.Sum(Table.SelectRows(#"Changed Type" , each [No]=i[No] and [Date]>Date.AddDays(i[Date],-365) and [Date]<=i[Date] ) [Value]), type number )
in #"Added Custom"

Or, 365 days before a specific date like 1980/12/31?

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MaxDate = #date(1980,12,31),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No", Int64.Type}, {"Value", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= MaxDate and [Date] > Date.AddDays(MaxDate,-365)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"No"}, {{"Total", each List.Sum([Value]), type number}})
in #"Grouped Rows"