Budgeting / Forecasting Help

[danielpaduck]

Hi,

I have an interesting issue.  I have a client that essentially wants me to spread the period cost value over from the start month to the end of the current year.  Thus, we have the following:

Start Date: 04/01/2024

End Date:  10/31/2025

Peirod Cost: 86480

The client is only budgeting during the current year (2024).  Thus, I need to take the 86480 and divide it by the number of months between 04/01/2024 and 10/31/2025 which is 19 months including the current. However, the issue, is that I need to take 4551 and put it into buckets / rows. Thus, in theory, something that would look like 

etc up to December 2024.  Not sure how to really do that.  Any help would be appreciated.  Maybe I need to do this in SQL instead of DAX? 

Thanks

[danielpaduck]

Thanks! I will check this out.  

[OwenAuger]

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

[danielpaduck]

Thanks! Yes, maybe in SQL might be better because this is a bit over my head for the Power Query.  Is the JSON consuming the one row in my example? 

[OwenAuger · Accepted solution]

No worries.

Yes, the Json.Document step was generated by using the Enter Data function in the Power Query user interface in order to input your sample row, however I realise that this obscures the intent.

Here is a version using the #table constructor instead (PBIX attached too).

This code can be pasted into a blank query in the Power Query advanced editor to see the steps.

let
  Source = 
    #table(
      type table[PlanID = text, StartDate = date, EndDate = date, PeriodCost = Currency.Type],
      {
        { "E28B8051205F462282A572F6DA375D5D", #date(2024,4,1), #date(2025,10,31), 4551.00 }
      }
    ),
  #"Added NumPeriods" = Table.AddColumn(
    Source,
    "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"