Forum Discussion
Merge two line chart with different timeline
I have a despatch plan from Jan to May & also the cash received data from Feb to Jun (Assuming 30 days credit period post despatch). Now in actual it has happened that some of the despatches of Apr month may get preponed and despatched in Feb. So the cash received for those items is at actual i.e. Desp Date + 30 Days. i.e. in Mar. Now if I want to plot both the graphs on cumulative value the time scale is different for both the values. So how to show them under one chart
| Month | Desp Plan | Cumulative Desp |
| Jan | 20 | 20 |
| Feb | 15 | 35 |
| Mar | 30 | 65 |
| Apr | 25 | 90 |
| May | 10 | 100 |
| TOTAL | 100 |
Hi abhi_kothekar ,
Here are the steps you can follow：
1. Create calculated table.
Table2 = CALENDAR( DATE( 2023,1,1), DATE( 2023,12,31))2. Create calculated table.
True = var _table1= SUMMARIZE( 'Table2','Table2'[Year],'Table2'[Month], "Cumulative1", SUMX( FILTER(ALL('Table'), YEAR('Table'[Desp Plan])='Table2'[Year]&& MONTH('Table'[Desp Plan])='Table2'[Month]),[Planned Value]), "Cumulative2", SUMX( FILTER(ALL('Table'), YEAR('Table'[Desp Actual])='Table2'[Year]&& MONTH('Table'[Desp Actual])='Table2'[Month]),[Desp value])) var _table2= ADDCOLUMNS( _table1,"Cumulative Plann", IF( [Cumulative1]=BLANK(),BLANK(), SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative1]))) return ADDCOLUMNS( _table2,"Cumulative Desp", IF( [Cumulative2]=BLANK(),BLANK(), SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative2])))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi abhi_kothekar ,
Here are the steps you can follow：
1. Create calculated table.
Table2 = CALENDAR( DATE( 2023,1,1), DATE( 2023,12,31))2. Create calculated table.
True = var _table1= SUMMARIZE( 'Table2','Table2'[Year],'Table2'[Month], "Cumulative1", SUMX( FILTER(ALL('Table'), YEAR('Table'[Desp Plan])='Table2'[Year]&& MONTH('Table'[Desp Plan])='Table2'[Month]),[Planned Value]), "Cumulative2", SUMX( FILTER(ALL('Table'), YEAR('Table'[Desp Actual])='Table2'[Year]&& MONTH('Table'[Desp Actual])='Table2'[Month]),[Desp value])) var _table2= ADDCOLUMNS( _table1,"Cumulative Plann", IF( [Cumulative1]=BLANK(),BLANK(), SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative1]))) return ADDCOLUMNS( _table2,"Cumulative Desp", IF( [Cumulative2]=BLANK(),BLANK(), SUMX(FILTER(_table1,[Year]=[Year]&&[Month]<=EARLIER([Month])),[Cumulative2])))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- lbendlin
Super User
Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?
Description Desp Plan Planned Value Desp Actual Desp value Assy 1 Jan-23 100 Jan-23 100 Assy 2 Jan-23 150 Mar-23 150 Assy 3 Feb-23 100 Feb-23 100 Assy 4 Mar-23 140 Feb-23 140 Assy 5 Mar-23 130 Apr-23 130 Assy 6 Apr-23 90 TBD Assy 7 May-23 80 TBD Assy 8 Jun-23 150 TBD Assy 9 Jun-23 130 TBD Assy 10 Jun-23 125 TBD See the above table. Now I want to plot cumulative line graph for planned vs actual.
