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 

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 20, 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