How to calculate daily totals if I have monthly values?

[Anonymous]

I have a table with monthly investment values ​​by region and metrics, I need to distribute those monthly investment values ​​into daily values. Any suggestions how I can achieve it.

[KNP]

A couple of examples depending on whether you want the data summarized or expanded to days.

Paste these into the advanced editor of a blank query.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMlTSUfJKzCtNLKoEskwNDAyUYnXgMm6pSUVQKXNzVCnfxKLkDCBtaGZiApSIBQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [y = _t, m = _t, value = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"y", Int64.Type}, {"m", type text}, {"value", type number}}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "DaysInMonth",
    each Date.DaysInMonth(Date.From("1/" & [m] & Text.From([y]))),
    Int64.Type
  ),
  #"Inserted Division" = Table.AddColumn(
    #"Added Custom",
    "DailyValue",
    each [value] / [DaysInMonth],
    type number
  ),
  #"Added Custom1" = Table.AddColumn(#"Inserted Division", "DayList", each {1 .. [DaysInMonth]}),
  #"Expanded DayList" = Table.ExpandListColumn(#"Added Custom1", "DayList"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DayList", {{"DayList", Int64.Type}})
in
  #"Changed Type1"

or this option for summarized...

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMlTSUfJKzCtNLKoEskwNDAyUYnXgMm6pSUVQKXNzVCnfxKLkDCBtaGZiApSIBQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [y = _t, m = _t, value = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"y", Int64.Type}, {"m", type text}, {"value", type number}}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "DaysInMonth",
    each Date.DaysInMonth(Date.From("1/" & [m] & Text.From([y]))),
    Int64.Type
  ),
  #"Inserted Division" = Table.AddColumn(
    #"Added Custom",
    "DailyValue",
    each [value] / [DaysInMonth],
    type number
  )
in
  #"Inserted Division"

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Otherwise, you can refer the solution in the following threads to achieve it:

Power Query solution:

Spreading monthly data by category across daily dates by category

DAX Solution:

Calculate daily targets based on monthly targets Sales Power bi

If the above ones can't help you get the expected result, please provide some sample data with Text format and your expected result with backend logic and special examples. Thank you.

Best Regards

[TomMartens]

Hey @Anonymous ,

there are some ways how you can "spread" your monthly values to a daily grain.

This depends on your exact requirements, if you want to distribute the monthly value to already existing daily values in another table, you will find this article helpful: Budget – DAX Patterns

If you want to transform your monthly table, into a daily table, you can use Power Query by creating a column that contains a table containing a table with all the days of the month, here you might need some extra columns, but this also works great.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom