Forum Discussion

Victor107200's avatar
Victor107200
Brass Contributor
Apr 19, 2021

Need help to create cash flow timeline

Hi,  My first post here and I hope somebody can help me out. I apologise if there is already a solution in this forum for my question. But I have been looking through hours of YouTube videos trying ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 19, 2021

Victor107200 

Perhaps something like

=SUMIFS(
    TblInköp[[First payment amount]:[First payment amount]],
    TblInköp[[Date first payment]:[Date first payment]],">="&P$2,
    TblInköp[[Date first payment]:[Date first payment]],"<="&EOMONTH(P$2,0)
)

for first payment amount and similar for the rest. Please check attached.

Victor107200's avatar
Victor107200
Brass Contributor
Apr 19, 2021

Thanks a lot Sergei!

This was very helpful. I think I might use your solution. But I think it would be even better if I could use pivot table (or power pivot if needed) because then I do not need to do the formula every time. Also, with a pivot table I would be able to easily change and group the date from days to, month or year or quarter etc.

Another advantage if there was a way to use a pivot table would be that I would then also be able to easily calculate the delivered quantity for each product for, say, every month. I know this was not a request in my first problem description, so I did not except anyone to solve this issue for me.

Maybe my idea of solving this using pivot table is wishful thinking. But if you do have another solution (with or without pivot table) which would also help me with the other challenge mentioned above, I would be even more grateful! Please see new attachment for reference.

Thanks in advance!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 19, 2021

    Victor107200 

    If you need something like this

    when creating PivotTable we need to add data to data model. First thing here we need separate Date (Calendar) table linked with our main table. I created with by Power Pivot but much better to use Power Query, there are a lot of patterns for that.

    After that we create relationships for each of dates in main table

    and build measures as

    First payment:=CALCULATE (
    SUM ( 'TblInköp'[First payment amount] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date first payment] )
)

Final payment:=CALCULATE (
    SUM ( 'TblInköp'[Final payment amount] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date final payment] )
)

Qty delivered:=CALCULATE (
    SUM ( 'TblInköp'[Qty] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'TblInköp'[Date delivery] )
)

Payment:=[First payment]+[Final payment]

    which we use in PivotTable.

    Please check in attached file.

    • Victor107200's avatar
      Victor107200
      Brass Contributor
      Apr 21, 2021

      SergeiBaklan 

       

      Thanks once again! I am very grateful for your help. But I still need some more help. I apologise for not being able to explain what I want to accomplish better. But I have made one more try. Please see attached document and the page “end result explanation”. Also, please see screen shot below for a quick preview. 

       

Resources