Forum Discussion
Monthly Budget to Working Days
- 2 years ago
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] ).
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])