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 to find the answer but without luck.  

 

Anyhow, I will try to explain what I need help with below. Also, please see attached example file for reference. Maybe it will make it clearer.

 

  1. I am trying to create a timeline from a data table where there is multiple dates that relates to different payments. I would like to show these payments on a timeline instead of a table.
  2. Also, I need to be able to easily edit/change the date in the timeline. That change should then also update the date in the timeline. At first I thought that it would be easy to build a simple pivot table timeline but this does not seam to be possible when I have several different dates on the same row.

It would really be fantastic if somebody could help be out and I would be very grateful. ‘

 

Thanks in advance!

excel

6 Replies

  • 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 19, 2021
      Thanks Nikolino! Unfortunantly this was not really what I was after. But I think Sergei Baklan may have given me a good start.

Resources