# Calculate Sum of Expression for each quarter

• ### Question

• I have a target table that has targets for a Business unit by quarter and type.

At any point of time in the year, I want to come up with a "Current Target" where

Target as of Today = SUMX("For Each Quarter", DIVIDE(Target for the Quarter, Number of Days in the Quarter) * Number of Days Elapsed in the Quarter)

I've kind of managed to get what I need using a connector table. Not sure if that's the most efficient/simple way to do it though so would appreciate if anyone can provide a better/simpler solution, ideally without the use of a connector table.

Sample File

Tuesday, July 5, 2016 6:25 PM

• You can use Power Query to create columns to your target table that contain these values:

```// Targets
let
Source = Excel.CurrentWorkbook(){[Name="Targets"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Org", type text}, {"Sub-Org", type text}, {"Type", type text}, {"Period", type text}, {"Target", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Period"},Calendar,{"Fiscal QQ-YYYY"},"NewColumn",JoinKind.LeftOuter),
#"Duplicated Column" = Table.DuplicateColumn(#"Merged Queries", "NewColumn", "NewColumn - Copy"),
#"Aggregated NewColumn" = Table.AggregateTableColumn(#"Duplicated Column", "NewColumn", {{"Full Date", List.Count, "DaysInPeriod"}}),
#"Added Custom" = Table.AddColumn(#"Aggregated NewColumn", "DaysElapsedInPeriod", each List.Count(Table.SelectRows([#"NewColumn - Copy"], each [Full Date] <= DateTime.LocalNow())[Full Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NewColumn - Copy"}),