locked
Calculate Sum of Expression for each quarter RRS feed

  • 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

Answers

  • 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"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Columns", "TargetAsOfToday", each [Target]/[DaysInPeriod]*[DaysElapsedInPeriod])
    in
        #"Added Custom1"

    
    


    Imke Feldmann TheBIccountant.com



    Sunday, July 10, 2016 2:58 PM
    Answerer