Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

How to calculate daily totals if I have monthly values?

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.

 

Alejanchav93_0-1633400540534.png

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

yingyinr_1-1635299769642.png

DAX Solution:

Calculate daily targets based on monthly targets Sales Power bi

yingyinr_2-1635299864713.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
KNP
Super User
Super User

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"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
v-yiruan-msft
Community Support
Community Support

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

yingyinr_1-1635299769642.png

DAX Solution:

Calculate daily targets based on monthly targets Sales Power bi

yingyinr_2-1635299864713.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.