Forum Discussion

THEG72's avatar
THEG72
Helper V
7 years ago
Solved

Generating CashFlow Forecast Tables

I have the following Table data containing cash inflow timings and amounts         In the example above, it shows 8.2 units for 5 months @ $194,103 which equates to $1,591,645 per month span...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi THEG72

     

    You may create a month table like below first. Then link the two table.

     

    You can get the table with measure like below:

    Measure =
CALCULATE (
    SUM ( Table2[Forecast Monthly Revenue] ),
    FILTER (
        ALL ( Table2 ),
        Table2[Finish Date] >= MAX ( 'Month'[Month] )
            && Table2[Start Date] <= MAX ( 'Month'[Month] )
    )
)

    Regards,
    Cherie

THEG72's avatar
THEG72
Helper V
7 years ago

hi v-cherch-msft


What i am after is the method to generate table data from the table information i have.

 

So in the first example Job T1S01, the table output would generate 5 cashflows (as the month span is 5 from Jan to May 2019) i need to generate in  table is as follows:

First Jobs Cashflow using Units per Month and Price Per Unit by Month Span

The Cashflow Amount and Dates takes into consideration the number of Monts the cashflow amount will span. The cashflow amount is simply the Units per Month Forecast multiply the Price per Unit

 

 

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi THEG72

     

    You may create a month table like below first. Then link the two table.

     

    You can get the table with measure like below:

    Measure =
CALCULATE (
    SUM ( Table2[Forecast Monthly Revenue] ),
    FILTER (
        ALL ( Table2 ),
        Table2[Finish Date] >= MAX ( 'Month'[Month] )
            && Table2[Start Date] <= MAX ( 'Month'[Month] )
    )
)

    Regards,
    Cherie