Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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] ).
Proud to be a Super User! | |
We've all been there!
Proud to be a 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.
Proud to be a Super User! | |
Hi ToddChitt,
Thanks for the info.
I tried this with
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] ).
Proud to be a Super User! | |
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:
Am I missing something here?
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.
Proud to be a Super User! | |
Ah silly me. I made it as a calculated column instead of measure. It works now! Thank you for your patience
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])
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.
Proud to be a Super User! | |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |