# Re: Generating CashFlow Forecast Tables

• ### 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

### 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

• Marked as answer by Monday, October 8, 2018 9:57 AM
Saturday, October 6, 2018 3:08 PM
• 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 Tuesday, October 9, 2018 1:43 AM updated
Monday, October 8, 2018 3:14 PM