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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

7 REPLIES 7
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 

 

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
Not applicable

@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
Super User
Super User

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 

 

This is really slick and good thinking ... I have a slightly different angle that's a wee more compllicated (I hope not) ... 

 

I have month forecasts and budgets across 60+ projects.

 

I want to use an in-memory Measure to create a daily budget broken down at each line item level). 

 

If that's not possible then I need to someone extend each Budget and each Forecast table.

 

  • I have a Calendar table which includes a line per day (this also has fiscal and other time intelligence features, i.e. weekday etc)
  • I have a Budget table
  • I have a Projects table
  • I have a forecast table (entered in days per month per resource)
  • I have a resource table (names of peeps)
  • I have a rates table (rates of those resources)

 

All these have the appropriate joins between them

 

I use Measures for all the $$$ Calculations ... so everything is done in memory not in tables/columns

 

Hence its not as easy as just create an additional column and divide the $$$ value by days ... 

 

My monthly budget formulae look something like this ... 

 

Financial Year Resource $ Measure (Fin Year is 1/7 - 30/06)

 

  • FY Forecast Resource $ = CALCULATE([Total Forecast Resource $], DATESYTD('Calendar'[Date],"30/06"))
  • Total Forecast Resource $ = SUMX('Forecast - Resource','Forecast - Resource'[Days] *[Resource Daily Gross $])
 
So all that is calculated on a Monthly basis because my monthly forecast days (i.e. 20) are all effectively on the 1st of the month.
 
And this is what the Forecast table looks like ... so I need to work out how to divide each of these by the working days in that MonthFiscalYear ... 
 
Forecast Table Details
Sulley_0-1643341473036.png


Do I do a table merge, bring in the calendar table, connect monthfiscalyear and expand the days with a filter on weekdays or something like that ... sounds right, but I have no idea how to code that properly.

 

And Calendar Table looks like this ... 

 
Calendar Table Details
Sulley_1-1643341649063.png

 

So trying to work out how to break it down to a daily split ... so I can filter at a daily level ... and not have a step jump in budget or forecast at the month beginning and end.
 
Your help much appreciated.
Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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