Need help to create cash flow timeline

%3CLINGO-SUB%20id%3D%22lingo-sub-2278240%22%20slang%3D%22en-US%22%3ENeed%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278240%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20first%20post%20here%20and%20I%20hope%20somebody%20can%20help%20me%20out.%20I%20apologise%20if%20there%20is%20already%20a%20solution%20in%20this%20forum%20for%20my%20question.%20But%20I%20have%20been%20looking%20through%20hours%20of%20YouTube%20videos%20trying%20to%20find%20the%20answer%20but%20without%20luck.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyhow%2C%20I%20will%20try%20to%20explain%20what%20I%20need%20help%20with%20below.%20Also%2C%20please%20see%20attached%20example%20file%20for%20reference.%20Maybe%20it%20will%20make%20it%20clearer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EI%20am%20trying%20to%20create%20a%20timeline%20from%20a%20data%20table%20where%20there%20is%20multiple%20dates%20that%20relates%20to%20different%20payments.%20I%20would%20like%20to%20show%20these%20payments%20on%20a%20timeline%20instead%20of%20a%20table.%3C%2FLI%3E%3CLI%3EAlso%2C%20I%20need%20to%20be%20able%20to%20easily%20edit%2Fchange%20the%20date%20in%20the%20timeline.%20That%20change%20should%20then%20also%20update%20the%20date%20in%20the%20timeline.%20At%20first%20I%20thought%20that%20it%20would%20be%20easy%20to%20build%20a%20simple%20pivot%20table%20timeline%20but%20this%20does%20not%20seam%20to%20be%20possible%20when%20I%20have%20several%20different%20dates%20on%20the%20same%20row.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIt%20would%20really%20be%20fantastic%20if%20somebody%20could%20help%20be%20out%20and%20I%20would%20be%20very%20grateful.%20%E2%80%98%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2278240%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279750%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20once%20again!%20I%20am%20very%20grateful%20for%20your%20help.%20But%20I%20still%20need%20some%20more%20help.%20I%20apologise%20for%20not%20being%26nbsp%3Bable%20to%20explain%20what%20I%20want%20to%20accomplish%20better.%20But%20I%20have%20made%20one%20more%20try.%20Please%20see%20attached%20document%20and%20the%20page%20%E2%80%9Cend%20result%20explanation%E2%80%9D.%20Also%2C%20please%20see%20screen%20shot%20below%20for%20a%20quick%20preview.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22preview.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274212i2647B393991F9D24%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22preview.jpg%22%20alt%3D%22preview.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278577%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1010739%22%20target%3D%22_blank%22%3E%40Victor107200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273953i915A26BC6262390F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%20creating%20PivotTable%20we%20need%20to%20add%20data%20to%20data%20model.%20First%20thing%20here%20we%20need%20separate%20Date%20(Calendar)%20table%20linked%20with%20our%20main%20table.%20I%20created%20with%20by%20Power%20Pivot%20but%20much%20better%20to%20use%20Power%20Query%2C%20there%20are%20a%20lot%20of%20patterns%20for%20that.%3C%2FP%3E%0A%3CP%3EAfter%20that%20we%20create%20relationships%20for%20each%20of%20dates%20in%20main%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20535px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273954i7E68BB5D451163FD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20build%20measures%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EFirst%20payment%3A%3DCALCULATE%20(%0A%20%20%20%20SUM%20(%20'TblInk%C3%B6p'%5BFirst%20payment%20amount%5D%20)%2C%0A%20%20%20%20USERELATIONSHIP%20(%20'Calendar'%5BDate%5D%2C%20'TblInk%C3%B6p'%5BDate%20first%20payment%5D%20)%0A)%0A%0AFinal%20payment%3A%3DCALCULATE%20(%0A%20%20%20%20SUM%20(%20'TblInk%C3%B6p'%5BFinal%20payment%20amount%5D%20)%2C%0A%20%20%20%20USERELATIONSHIP%20(%20'Calendar'%5BDate%5D%2C%20'TblInk%C3%B6p'%5BDate%20final%20payment%5D%20)%0A)%0A%0AQty%20delivered%3A%3DCALCULATE%20(%0A%20%20%20%20SUM%20(%20'TblInk%C3%B6p'%5BQty%5D%20)%2C%0A%20%20%20%20USERELATIONSHIP%20(%20'Calendar'%5BDate%5D%2C%20'TblInk%C3%B6p'%5BDate%20delivery%5D%20)%0A)%0A%0APayment%3A%3D%5BFirst%20payment%5D%2B%5BFinal%20payment%5D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20we%20use%20in%20PivotTable.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278426%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278426%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%20Sergei!%3C%2FP%3E%3CP%3EThis%20was%20very%20helpful.%20I%20think%20I%20might%20use%20your%20solution.%20But%20I%20think%20it%20would%20be%20even%20better%20if%20I%20could%20use%20pivot%20table%20(or%20power%20pivot%20if%20needed)%20because%20then%20I%20do%20not%20need%20to%20do%20the%20formula%20every%20time.%20Also%2C%20with%20a%20pivot%20table%20I%20would%20be%20able%20to%20easily%20change%20and%20group%20the%20date%20from%20days%20to%2C%20month%20or%20year%20or%20quarter%20etc.%3C%2FP%3E%3CP%3EAnother%20advantage%20if%20there%20was%20a%20way%20to%20use%20a%20pivot%20table%20would%20be%20that%20I%20would%20then%20also%20be%20able%20to%20easily%20calculate%20the%20delivered%20quantity%20for%20each%20product%20for%2C%20say%2C%20every%20month.%20I%20know%20this%20was%20not%20a%20request%20in%20my%20first%20problem%20description%2C%20so%20I%20did%20not%20except%20anyone%20to%20solve%20this%20issue%20for%20me.%3C%2FP%3E%3CP%3EMaybe%20my%20idea%20of%20solving%20this%20using%20pivot%20table%20is%20wishful%20thinking.%20But%20if%20you%20do%20have%20another%20solution%20(with%20or%20without%20pivot%20table)%20which%20would%20also%20help%20me%20with%20the%20other%20challenge%20mentioned%20above%2C%20I%20would%20be%20even%20more%20grateful!%20Please%20see%20new%20attachment%20for%20reference.%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278403%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278403%22%20slang%3D%22en-US%22%3EThanks%20Nikolino!%20Unfortunantly%20this%20was%20not%20really%20what%20I%20was%20after.%20But%20I%20think%20Sergei%20Baklan%20may%20have%20given%20me%20a%20good%20start.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278343%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1010739%22%20target%3D%22_blank%22%3E%40Victor107200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(%0A%20%20%20%20TblInk%C3%B6p%5B%5BFirst%20payment%20amount%5D%3A%5BFirst%20payment%20amount%5D%5D%2C%0A%20%20%20%20TblInk%C3%B6p%5B%5BDate%20first%20payment%5D%3A%5BDate%20first%20payment%5D%5D%2C%22%26gt%3B%3D%22%26amp%3BP%242%2C%0A%20%20%20%20TblInk%C3%B6p%5B%5BDate%20first%20payment%5D%3A%5BDate%20first%20payment%5D%5D%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(P%242%2C0)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20first%20payment%20amount%20and%20similar%20for%20the%20rest.%20Please%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278280%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20create%20cash%20flow%20timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1010739%22%20target%3D%22_blank%22%3E%40Victor107200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EMaybe%20that%20will%20help%20you%20a%20little%20further%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-1303322828%22%20id%3D%22toc-hId-1303322828%22%3E%3CA%20href%3D%22https%3A%2F%2Ftemplates.office.com%2Fen-us%2Fprofit-loss-statement-tm45130229%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EProfit%20loss%20statement%3C%2FA%3E%3C%2FH1%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20patience%20and%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day%20%2F%20night%20with%20lots%20of%20health%2C%20joy%20and%20love.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

6 Replies

@Victor107200 

Maybe that will help you a little further

Profit loss statement

 

Thank you for your patience and time.

 

Wish you a nice day / night with lots of health, joy and love.

 

Nikolino

I know I don't know anything (Socrates)

@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.

Thanks Nikolino! Unfortunantly this was not really what I was after. But I think Sergei Baklan may have given me a good start.

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!

@Victor107200 

If you need something like this

image.png

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

image.png

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.

@Sergei Baklan 

 

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. 

 

preview.jpg