Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Monthly Budget to Working Days

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 ...
  • ToddChitt's avatar
    ToddChitt
    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] ).