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] ).
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 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?
- ToddChitt2 years ago
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.
- Anonymous2 years agoNot applicable
Ah silly me. I made it as a calculated column instead of measure. It works now! Thank you for your patience