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

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
Adamtall
Resolver III
Resolver III

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
Adamtall
Resolver III
Resolver III

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

Anonymous
Not applicable

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]
 
 

@Anonymous 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?

 

 

ToddChitt
Post Prodigy
Post Prodigy

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.

 

 

@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)  🙂

 

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors