none
Re: Generating CashFlow Forecast Tables RRS feed

  • Question

  • Hi Guys,

    I want to generate a table of cashflows data by date like so

    Job name Cashflow Date Cashflow Amount
    T1S01 31/01/2019  $             1,591,644.60
    T1S01 28/02/2019  $             1,591,644.60
    T1S01 31/03/2019  $             1,591,644.60
    T1S01 30/04/2019  $             1,591,644.60
    T1S01 31/05/2019  $             1,591,644.60

    I have the following source table data.

    Job name Start Date End Date Month Span Total Units Price Per Unit Units Per month Cashflow Monthly
    T1S01 1/01/2019 31/05/2019                    5                41  $ 194,103.00                      8.20  $          1,591,644.60
    T1S02 1/08/2021 30/09/2021                    2                21  $ 195,250.00                    10.50  $          2,050,125.00
    T1S03 1/12/2019 31/03/2020                    4                38  $ 175,000.00                      9.50  $          1,662,500.00

    What is the best approach to produce the monthly cashflows in a table for each project and each months cashflow?

    thanks for your help and time!

    Thursday, October 4, 2018 7:59 AM

Answers

All replies

  • You can use the function from this blogpost to create a column to your source-table that generates on date field per month. Then expand that column into multiple rows: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Garry72 Monday, October 8, 2018 9:57 AM
    Saturday, October 6, 2018 3:08 PM
    Moderator
  • Thanks Imke, I will give this a try and read through your Blog example!
    Monday, October 8, 2018 9:58 AM
  • Imke, this worked really well to give all the months in the range.

    Is there of way of also adding to your function the number of months and units for each month span in the cashflow lines?

    So in the example above I need Month Span = 5 months so I can then work out Units per month? for example 8.2 units over 5 months to give the 41 total units for the first job T1S01 between 01/01/19 to 31/05/2019.


    • Edited by Garry72 Tuesday, October 9, 2018 1:43 AM updated
    Monday, October 8, 2018 3:14 PM