Spread costs across months
Hi, I have seen different iterations of this in posts but can't seem to find an option that works for me. I'm trying to calculate the monthly spend on apprentices based on their start and end date. The cost should be attributed as follows: Between start and end date 80% of the apprenticship total should be paid. (divide 80% of total cost by number of months) The month after apprentice ends pay the final 20%. the number of days in the month is irrelivant i.e if there is at least one day in a month it should be added to the total number of months. as an example: A £100 apprenticship that lasts 10 months, ending in September should show: £8 per month for 10 months ending in September £20 spend occuring in October. The tricky bit is the length, start/end and cost can be different everytime. I would like to be able to display this in a matrix or table and a line or column chart, ideally both but will take what I can get if there are limitations for any reason. My data is laid out as follows: Both relationships are inactive Planned end date * - 1 Date Planned start date * - 1 Date The tables are as follows: Period (the date range covers all relevant dates) FactApprentice Duration Months, 80% agreed price, 20% agreed price are calcualted columns. Ideally I would like to be able to do this without adding another table but if required it can be. Please cask for more detail if needed.
