cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Monthly Budget to Working Days

Dear All,

I am looking to divide my monthly budget into a working day budget. I have the following columns with a cumulative work days & the # of working days in the budget table.

I've then attempted to do a MTD sales per cumulative day, by dividing the sales by # of working days (from Budget Table) and then multiply by the # of cumulative days to have a daily budget. This works in the table on the right but when combining with sales in the main graph, I get a very strange summised line, instead of 2 lines close to each other. Please can you help with this?

1 ACCEPTED SOLUTION
Super User

I don't think you fully understand the proposed solution. You have Budget data at a MONTHLY grain like this:

Month       Budget      Working Days

====        =====     =========

1               22,000        22

2               21,000        21

And you have Dates like this:

Month     Date    Working Day

====      ====  ========

1             1/1       0

1             1/2       1

1             1/3       1

You need to JOIN the two tables on Month (and Year):

Month     Budget     Date     Working Day      Working Days

=====    =====   ====   =======         =========

1               22,000     1/1       0                         22

1               22,000     1/2       1                         22

...

2               21,000     2/1       1                         21

Now create a calculated column in this table:

[Daily Budget] = [Monthly Budget] / [Working Days] * [Working Day]

You don't need SUM here! If using this sample data, the [Daily Budget should come out to 1,000 per working day, and 0 for non-working days.

Now the MTD calculation uses the SUM function:

[MTD Budget] = TOTALMTD ( SUM ( [Daily Budget] ), 'Date Dimension'[Date] ).

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

9 REPLIES 9
Super User

We've all been there!

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Super User

What I have done in the past is to join the Budget table to the Dates table on the Month and Year. That will essentially 'blow up' the Budget table to have a Budget row for every day. But that budget number is the MONTHLY budget, not the Daily Budget. As stated before, the formula for [Daily Budget] = [Month Budget] / [Working Days] * [Working Day]. That will give you a 1/20th (or there abouts) budget value for each working day, but because you multiply it by the Working Day flag, (1 or 0) the weekends get \$0 budget.

You need the data at this daily granularity.

Now create a calculation for MTD Budget = TOTALMTD( [Daily Budget], 'Dates'[Date] ) and line plot that on the Y axis against the Date on the X axis.

A couple of things:

Your Date dimension should have all dates, which it looks like it does.

You do not need Cumulative Working Days.

When you plot it, you should see plateaus at the non-working weekends.

Hope that helps.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

Hi ToddChitt,

Thanks for the info.

I tried this with

Daily Budget = (SUM('UK Monthly Budget'[Budget FY24])/SUM('UK Monthly Budget'[Working Days]))
& then
MTDBudget = TOTALMTD(SUM('UK Monthly Budget'[Daily Budget]),'Working Day'[Invoice Date])

However, the sum function doesn't work. (Changed format to Number) I get the same value for all dates and now sum when in a graph:

Super User

I don't think you fully understand the proposed solution. You have Budget data at a MONTHLY grain like this:

Month       Budget      Working Days

====        =====     =========

1               22,000        22

2               21,000        21

And you have Dates like this:

Month     Date    Working Day

====      ====  ========

1             1/1       0

1             1/2       1

1             1/3       1

You need to JOIN the two tables on Month (and Year):

Month     Budget     Date     Working Day      Working Days

=====    =====   ====   =======         =========

1               22,000     1/1       0                         22

1               22,000     1/2       1                         22

...

2               21,000     2/1       1                         21

Now create a calculated column in this table:

[Daily Budget] = [Monthly Budget] / [Working Days] * [Working Day]

You don't need SUM here! If using this sample data, the [Daily Budget should come out to 1,000 per working day, and 0 for non-working days.

Now the MTD calculation uses the SUM function:

[MTD Budget] = TOTALMTD ( SUM ( [Daily Budget] ), 'Date Dimension'[Date] ).

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

Hi ToddChitt, thanks for the quick response. I understood what you meant now and created this. Yes you're correct, it blows up the new budget table into a daily budget figure for each row

The daily budget is as :

Daily Budget = Budget_WorkingDay[Budget FY24]/Budget_WorkingDay[Working Days]* Budget_WorkingDay[Working Day.Working Day]

The MTD Budget is:

MTD Budget = TOTALMTD(SUM(Budget_WorkingDay[Daily Budget]),Budget_WorkingDay[Working Day.Invoice Date])

However the MTD Budget is not a rolling number and I get the same numbers Daily and MTD:

Am I missing something here?

Super User

This may seem like an obvious question but I have to ask anyway:

Did you create the MTD Budget as a MEASURE, or as a Calculated Column? It needs to be a Measure so it can operate on the entire table.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

Ah silly me. I made it as a calculated column instead of measure. It works now! Thank you for your patience

Helper I

Hi, this is my working day table and budget table:

Working Day Table

 Invoice Date Weekday Working Day Cumulative Days Financial Year 01/03/2024 5 1 1 FY24 - Q3 02/03/2024 6 0 1 FY24 - Q3 03/03/2024 7 0 1 FY24 - Q3 04/03/2024 1 1 2 FY24 - Q3 05/03/2024 2 1 3 FY24 - Q3 06/03/2024 3 1 4 FY24 - Q3 07/03/2024 4 1 5 FY24 - Q3 08/03/2024 5 1 6 FY24 - Q3 09/03/2024 6 0 6 FY24 - Q3 10/03/2024 7 0 6 FY24 - Q3 11/03/2024 1 1 7 FY24 - Q3 12/03/2024 2 1 8 FY24 - Q3 13/03/2024 3 1 9 FY24 - Q3 14/03/2024 4 1 10 FY24 - Q3 15/03/2024 5 1 11 FY24 - Q3 16/03/2024 6 0 11 FY24 - Q3 17/03/2024 7 0 11 FY24 - Q3 18/03/2024 1 1 12 FY24 - Q3 19/03/2024 2 1 13 FY24 - Q3 20/03/2024 3 1 14 FY24 - Q3 21/03/2024 4 1 15 FY24 - Q3 22/03/2024 5 1 16 FY24 - Q3 23/03/2024 6 0 16 FY24 - Q3 24/03/2024 7 0 16 FY24 - Q3 25/03/2024 1 1 17 FY24 - Q3 26/03/2024 2 1 18 FY24 - Q3 27/03/2024 3 1 19 FY24 - Q3 28/03/2024 4 1 20 FY24 - Q3 29/03/2024 5 0 20 FY24 - Q3 30/03/2024 6 0 20 FY24 - Q3 31/03/2024 7 0 20 FY24 - Q3

Budget Table:

 Product Month Budget FY24 Financial Year Working Days A Jul-23 269,372 FY24 - Q1 21 A Aug-23 283,996 FY24 - Q1 22 A Sep-23 264,235 FY24 - Q1 21 A Oct-23 289,872 FY24 - Q2 22 A Nov-23 296,478 FY24 - Q2 22 A Dec-23 269,559 FY24 - Q2 19 A Jan-24 279,327 FY24 - Q3 22 A Feb-24 286,271 FY24 - Q3 21 A Mar-24 296,318 FY24 - Q3 20 A Apr-24 284,535 FY24 - Q4 22 A May-24 328,459 FY24 - Q4 21 A Jun-24 319,776 FY24 - Q4 21

I have the working day table that goes up to end of 2026.

My formula is below, but I believe it is inflating all the Cumulative day values by summing and multiplying by the monthly budget for each day of the month. Any Powerquery to get around this would be great, as I have added the Working Days in the manual file instead would be great to avoid this inflation.

MTD Budget = (SUM('UK Monthly Budget'[Budget FY24])/SUM('UK Monthly Budget'[Working Days]))*SUM('Working Day'[Cumulative Days])

Super User

Can you supply a sample of the raw data, NOT as summarized in a table visual?

For breaking up a monthly budget into its day component, sounds like you  did the math right. But raw data would help.

Daily Budget for the month = Monthly Budget divided by the number of working days in the month.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors