Budgeting / Forecasting Help

Hi danielpaduck 

If we assume that you're always dealing in whole periods that each receive the same cost allocation, here's a simple example in Power Query (I would not recommend DAX for this sort of thing):

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JcexDcAgDATAXVzHkv34wS0RsARi/zWSKNI1t7dM5J1Gh3FFBRKdDauOXhoHh1wCQ6i9/A/VTcuXIF3OeQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [PlanID = _t, StartDate = _t, EndDate = _t, PeriodCost = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"PlanID", type text},
      {"StartDate", type date},
      {"EndDate", type date},
      {"PeriodCost", Currency.Type}
    }
  ),
  #"Added NumPeriods" = Table.AddColumn(
    #"Changed Type",
    "NumPeriods",
    each (Date.Year([EndDate]) - Date.Year([StartDate]))
      * 12 + Date.Month([EndDate]) - Date.Month([StartDate]) + 1,
    Int64.Type
  ),
  #"Added Date List" = Table.AddColumn(
    #"Added NumPeriods",
    "Date",
    each
      let
        StartDate = [StartDate]
      in
        List.Transform(
          {0 .. [NumPeriods] - 1},
          each Date.AddMonths(Date.StartOfMonth(StartDate), _)
        ),
    type {date}
  ),
  #"Expanded Date List" = Table.ExpandListColumn(#"Added Date List", "Date"),
  #"Removed Columns" = Table.RemoveColumns(
    #"Expanded Date List",
    {"NumPeriods", "StartDate", "EndDate"}
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date", "StartDate"}}),
  #"Added EndDate" = Table.AddColumn(
    #"Renamed Columns",
    "EndDate",
    each Date.EndOfMonth([StartDate]),
    type date
  )
in
  #"Added EndDate"

Note that you may only require one of StartDate or EndDate in the model.

If pushing upstream to SQL is an option, then that would probably be preferable.

Someone may want to chime in with the equivalent transformation in SQL 🙂

Regards