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?
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! | |
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! | |
Helpful resources
Power BI Monthly Update - October 2024
Check out the October 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - October 2024
Find out what's new and trending in the Fabric Community.
