cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hede92 Helper I
Helper I

Divide monthly budget across all days in the month

Hi all,

 

I am having problems dividing my monthly budget across my days.

 

I have a calendar table and a table containing the budget numbers.

They are connected with a 1 to * relationship.

 

the budget table looks like this.

PRODUCTDATEBUDGET
A01-01-2020100
B01-01-2020300
A01-02-2020100
B01-02-2020300

 

Id like to summarize all budget numbers for the month and then divide them across all the dates of the specific month in my calendar table.

 

So if we take January it would be:
Total budget: 100 + 300 = 400

Divided by days in January: 400 / 31 = 12.9

Then put in a column in my calendar table

01-01-202012.9
02-01-202012.9
03-01-202012.9
......

 

It sounds so simple to me, but i cant for the life of me find the solution.

1 ACCEPTED SOLUTION

Accepted Solutions
Adamtall Resolver II
Resolver II

Re: Divide monthly budget across all days in the month

Hi,

Go to advance editor in PowerQuery and type in. (So you can see the follwing steps in PowerBI)

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIwMjAyDH0MBAKVYnWskJXcIYKgHWYYRLhxG6jlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Budget = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Budget", Int64.Type}}),
#"Inserted End of Month" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted End of Month", "Last Characters", each Text.End(Text.From([End of Month], "sv-SE"), 2), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "ListAllDates", each { Number.From([Date])..Number.From([End of Month]) }),
#"Inserted Division" = Table.AddColumn(#"Added Custom", "Division", each [Budget] / [Last Characters], type number),
#"Expanded ListAllDates" = Table.ExpandListColumn(#"Inserted Division", "ListAllDates"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded ListAllDates",{{"ListAllDates", type date}})
in
#"Changed Type2"

 

 

This will work for all products and not only a total.

You can change the the column names so it matches columns in Fact table and then append it in the facts table.

 

 

/Adam 

 

View solution in original post

6 REPLIES 6
ToddChitt Helper V
Helper V

Re: Divide monthly budget across all days in the month

I have done this on a few models now and it's not that hard. Basically you have to explode your monthly Budget table from 12 rows per year to 365 rows per year.

 

Start in Power Query. Make a duplicate of your Date query and add a step to aggregate it by Year and Month. Add and aggregation for counting the number of days. Now do a Merge operation on your main Date table to join to this one, expand it to include only the aggregate column of [Number of days in the month]. You now have, for each calendar date, a column that show the number of days in the respective month.

 

Now take your Budget table and merge it with the Date table, again on Year and Month. Expand this to include the [Number of days in the month] from the merged Date table. This now becomes the new 'Daily Budget' table. When you do, it will go from 12 rows to 365 rows. All that is needed is a calculated column (for each Day) to be [Monthly Budget] divided by [Number of days in the Month] = [Daily Budget].

 

I typically take it one step further and include a [Working Day] column in my Date table that is 1 for Monday through Friday, then 0 for weedends. The aggregate is SUM([Working Day]) and the result is usually about 20 or 21 per month. Then the Calculation for Daily Budget = [Working Day] * [Monthly Budget] / [Number of WORKING Days in the Month]

 

Hope that helps.

 

 

Highlighted
Priyadarshuan
Regular Visitor

Re: Divide monthly budget across all days in the month

Add 2 columns sum and days in calander table , change both column data types to decimal numbers and add a third column dividing them both.

 

Sum =
VAR mon = month('calendar'[date])
RETURN
SUMX(CALCULATETABLE('budget_table_name',
                                          MONTH('budget_table_name'[DATE])=mon),
           'budget_table_name'[BUDGET])

Days =
VAR mon = month('calendar'[date])
VAR y = YEAR('calendar'[date])
RETURN
DAY(
IF(
mon = 12,
DATE(y+1,1,1),
DATE(y, mon + 1, 1)
) - 1
)
 
Column 3 = 'calendar'[Sum]/'calendar'[Days]
 
 
Hede92 Helper I
Helper I

Re: Divide monthly budget across all days in the month

@Priyadarshuan Thanks for the reply,

 

I cant get it to ignore the day of the month, so the calculations only work for the first each month.

Is there a way to make it ignore the day and just check on the month and year?

 

 

Hede92 Helper I
Helper I

Re: Divide monthly budget across all days in the month

@ToddChitt Thanks for the reply,

 

I have many lines for each months budget. I tried to reflect that in my example.

 

Lets say i have 20 different budget numbers for each month. I would have to explode all 20 lines.

 

I'm looking for a way to summarize the budget numbers for each month and then spread it evenly across the days in the month in my calendar table. It just needs to work on a weekly basis, so whether its a work day or not is irellevant. (But i will save that little tip for later use)  🙂

 

Adamtall Resolver II
Resolver II

Re: Divide monthly budget across all days in the month

Hi,

Go to advance editor in PowerQuery and type in. (So you can see the follwing steps in PowerBI)

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIwMjAyDH0MBAKVYnWskJXcIYKgHWYYRLhxG6jlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Budget = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Budget", Int64.Type}}),
#"Inserted End of Month" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted End of Month", "Last Characters", each Text.End(Text.From([End of Month], "sv-SE"), 2), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "ListAllDates", each { Number.From([Date])..Number.From([End of Month]) }),
#"Inserted Division" = Table.AddColumn(#"Added Custom", "Division", each [Budget] / [Last Characters], type number),
#"Expanded ListAllDates" = Table.ExpandListColumn(#"Inserted Division", "ListAllDates"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded ListAllDates",{{"ListAllDates", type date}})
in
#"Changed Type2"

 

 

This will work for all products and not only a total.

You can change the the column names so it matches columns in Fact table and then append it in the facts table.

 

 

/Adam 

 

View solution in original post

ToddChitt Helper V
Helper V

Re: Divide monthly budget across all days in the month

Many rows shouldn't matter. I have Power BI models with up to 60 million rows of data and they still work fine.

 

But if you really want to consolidate, in Power Query, after you MERGE your Budget and MONTH tables and have exploded all the rows, do a GROUP BY operation next and GROUP BY Date and SUM the [Daily Budget] number. Note that you may also want to group by Product and/or any other dimension field in the table.

 

Hope that helps.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors