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