Cumulative Total on Line Chart

Question

Hello,&nbsp;&nbsp;Once again I am stuck with PowerBI and need some help.&nbsp;&nbsp;I have some calculated columns that return a value (number 1) if certain conditions in other colums&nbsp; are true:&nbsp;SG4 Baseline = IF(SW_Master_Programme_CSV[MS Type]="SG4: Detailed Design Complete" &amp;&amp; SW_Master_Programme_CSV[MS Baseline Year]="2019/2020" &amp;&amp; SW_Master_Programme_CSV[Date Type]="Baseline Start",1,0)&nbsp;SG4 Complete = IF(SW_Master_Programme_CSV[MS Type]="SG4: Detailed Design Complete" &amp;&amp; SW_Master_Programme_CSV[MS Baseline Year]="2019/2020" &amp;&amp; SW_Master_Programme_CSV[Date Type]="Actual Finish" &amp;&amp; SW_Master_Programme_CSV[Percent Complete As Text]="1",1,0)&nbsp;I have then have two measures:&nbsp;&nbsp;SUM SG4 Baseline = SUM(SW_Master_Programme_CSV[SG4 Baseline])SUM SG4 Baseline = SUM(SW_Master_Programme_CSV[SG4 Baseline])&nbsp;I am plotting the two measures on a line chart. The Axis is 'MonthNameShort' which is linked to a date table etc.&nbsp;&nbsp;In my date table I've made a fiscal year column and sorted 'MonthNameShort' by that.&nbsp;&nbsp;The result I am getting is as follows:&nbsp;I think I am on the right track but what I actually need is a cumulative total month on month for each line - e.g. May would be the sum of both April and May results from my measures.&nbsp;&nbsp;Any help much appreciated

anonymous · Answer

I have tried the following two measuresXXXX =CALCULATE(SUM(SW_Master_Programme_CSV[SG4 Baseline]),FIlter(ALLSELECTED(SW_Master_Programme_CSV),SW_Master_Programme_CSV[Date]&lt;= MAX(SW_Master_Programme_CSV[Date])))&nbsp;XXXX =CALCULATE(SUM(SW_Master_Programme_CSV[SG4 Baseline]),FIlter(ALLSELECTED(SW_Master_Programme_CSV),SW_Master_Programme_CSV[Date]&lt;= MAX(SW_Master_Programme_CSV[Date]))&nbsp;I then plotted them on a line chart&nbsp;&nbsp;The only way I could get the desired effect was to add a date slicer by my 'Date' field from the date table - This was discovered purely by chance, I have no idea why this is working or why I need to use a slicer.&nbsp;Any explanation much appreciated&nbsp;

v-piga-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
By my tests with your formula, I cannot reproduce your issue.
Will you have this issue if you only drag one measure on the line chart?
In addition, to solve your problem quickly, please share the data sample which could reproduce the scenario so that we could have a test on it.
Best&nbsp; Regards,
Cherry

anonymous · Answer

v-piga-msft&nbsp; thank you for your response&nbsp;Here is a small example of the data (with anything sensitive removed but that makes no difference to the formulas as all required fields are below)&nbsp;I have unpivoted all of the dates field to give me one column of 'Dates', this is then linked by a relationship to a full date table - I believe this is the correct action as I want to plot on the graph:&nbsp;1. The cumulative number of SG4 milestones each month according to baseline start dates2. The cumulative number of SG4's that are complete each month according to actual finish dates&nbsp;As I said in the post above, I can get the desired result if I use a slicer and only specific dates for this financial year - 01/04/2019 to 31/03/2020 - No idea why, maybe I just need to use the slicer?&nbsp;PINBaseline_StartStart_DateActual_StartBaseline_FinishFinish_DateActual_FinishTask_TypeMS_BL_YearPercent_CompleteMS_Type601875Thu 13/12/18Thu 13/12/18Thu 13/12/18Thu 09/04/20Wed 27/05/20&nbsp;&nbsp;&nbsp;44%&nbsp;601875Thu 13/12/18Thu 13/12/18Thu 13/12/18Tue 04/06/19Tue 04/06/19Tue 04/06/19Design Task&nbsp;100%&nbsp;601875Tue 04/06/19Tue 04/06/19Tue 04/06/19Fri 28/06/19Mon 22/07/19Mon 22/07/19Commercial Task&nbsp;100%&nbsp;601875Mon 01/07/19Mon 15/07/19Mon 15/07/19Mon 15/07/19Fri 26/07/19Fri 26/07/19Mobilisation Task&nbsp;100%&nbsp;601875Mon 15/07/19Mon 29/07/19Mon 29/07/19Wed 17/07/19Thu 01/08/19Thu 01/08/19Build Task&nbsp;100%&nbsp;601875Wed 17/07/19Thu 01/08/19Thu 01/08/19Wed 17/07/19Thu 01/08/19Thu 01/08/19Build Task&nbsp;100%&nbsp;601875Mon 10/06/19Mon 29/07/19&nbsp;Fri 03/01/20Mon 03/02/20&nbsp;Design Task&nbsp;0%&nbsp;601875Fri 03/01/20Mon 03/02/20&nbsp;Fri 03/01/20Mon 03/02/20&nbsp;SG4 Milestone2019/20200%SG4: Detailed Design Complete601875Mon 06/01/20Tue 04/02/20&nbsp;Fri 28/02/20Mon 30/03/20&nbsp;Commercial Task&nbsp;0%&nbsp;601875Fri 28/02/20Mon 30/03/20&nbsp;Fri 28/02/20Mon 30/03/20&nbsp;SG5 Milestone2019/20200%SG5: Target Cost Achieved601875Mon 02/03/20Tue 31/03/20&nbsp;Fri 13/03/20Tue 28/04/20&nbsp;Mobilisation Task&nbsp;0%&nbsp;601875Mon 16/03/20Wed 29/04/20&nbsp;Thu 09/04/20Wed 27/05/20&nbsp;Build Task&nbsp;0%&nbsp;601875Thu 09/04/20Wed 27/05/20&nbsp;Thu 09/04/20Wed 27/05/20&nbsp;SG6 Milestone2020/20210%SG6: Construction Complete603767Mon 04/03/19Mon 04/03/19Mon 04/03/19Fri 26/07/19Mon 02/09/19&nbsp;&nbsp;&nbsp;95%&nbsp;603767Mon 04/03/19Mon 04/03/19Mon 04/03/19Fri 26/07/19Mon 17/06/19Mon 17/06/19Design Task&nbsp;100%&nbsp;603767Mon 04/03/19Mon 04/03/19Mon 04/03/19Wed 05/06/19Wed 05/06/19Wed 05/06/19Design Task&nbsp;100%&nbsp;603767Thu 23/05/19Thu 23/05/19Thu 23/05/19Mon 01/07/19Wed 10/07/19Wed 10/07/19&nbsp;&nbsp;100%&nbsp;603767Wed 05/06/19Mon 17/06/19Mon 17/06/19Wed 05/06/19Mon 17/06/19Mon 17/06/19SG4 Milestone2019/2020100%SG4: Detailed Design Complete603767Thu 06/06/19Thu 20/06/19Thu 20/06/19Fri 28/06/19Wed 24/07/19Wed 24/07/19Commercial Task&nbsp;100%&nbsp;603767Fri 28/06/19Wed 24/07/19Wed 24/07/19Fri 28/06/19Wed 24/07/19Wed 24/07/19SG5 Milestone2019/2020100%SG5: Target Cost Achieved603767Mon 01/07/19Mon 15/07/19Mon 15/07/19Fri 12/07/19Fri 26/07/19Fri 26/07/19Mobilisation Task&nbsp;100%&nbsp;603767Mon 15/07/19Mon 29/07/19Mon 29/07/19Fri 26/07/19Mon 02/09/19&nbsp;Build Task&nbsp;55%&nbsp;603767Fri 26/07/19Mon 02/09/19&nbsp;Fri 26/07/19Mon 02/09/19&nbsp;SG6 Milestone2019/20200%SG6: Construction Complete601874Thu 15/08/19Wed 16/10/19&nbsp;Thu 21/11/19Tue 04/02/20&nbsp;&nbsp;&nbsp;0%&nbsp;601874Thu 15/08/19Wed 16/10/19&nbsp;Thu 10/10/19Tue 10/12/19&nbsp;Commercial Task&nbsp;0%&nbsp;601874Thu 10/10/19Tue 10/12/19&nbsp;Thu 10/10/19Tue 10/12/19&nbsp;SG5 Milestone2019/20200%SG5: Target Cost Achieved601874Fri 11/10/19Wed 11/12/19&nbsp;Thu 07/11/19Tue 21/01/20&nbsp;Mobilisation Task&nbsp;0%&nbsp;601874Fri 08/11/19Wed 22/01/20&nbsp;Thu 21/11/19Tue 04/02/20&nbsp;Build Task&nbsp;0%&nbsp;601874Thu 21/11/19Tue 04/02/20&nbsp;Thu 21/11/19Tue 04/02/20&nbsp;SG6 Milestone2019/20200%SG6: Construction Complete601873Mon 15/07/19Mon 01/07/19Mon 01/07/19Mon 21/10/19Mon 16/12/19&nbsp;&nbsp;&nbsp;38%&nbsp;

anonymous · Answer

If i remove the slicer I get the below result, the number of measures seems to have no effect:&nbsp;&nbsp;Is it something to do with my axis? Can the cumulative formula not plot the cumulative totals over that field?

anonymous · Answer

v-piga-msft&nbsp;- Any ideas from my comments above?

Forum Discussion

Cumulative Total on Line Chart

5 Replies

Related content

Cumulative Total

Cumulative Total

SumX? - cumulative running total

Cumulative Total in Stacked Bar Chart not working

Cumulative Total

PIN	Baseline_Start	Start_Date	Actual_Start	Baseline_Finish	Finish_Date	Actual_Finish	Task_Type	MS_BL_Year	Percent_Complete	MS_Type
601875	Thu 13/12/18	Thu 13/12/18	Thu 13/12/18	Thu 09/04/20	Wed 27/05/20				44%
601875	Thu 13/12/18	Thu 13/12/18	Thu 13/12/18	Tue 04/06/19	Tue 04/06/19	Tue 04/06/19	Design Task		100%
601875	Tue 04/06/19	Tue 04/06/19	Tue 04/06/19	Fri 28/06/19	Mon 22/07/19	Mon 22/07/19	Commercial Task		100%
601875	Mon 01/07/19	Mon 15/07/19	Mon 15/07/19	Mon 15/07/19	Fri 26/07/19	Fri 26/07/19	Mobilisation Task		100%
601875	Mon 15/07/19	Mon 29/07/19	Mon 29/07/19	Wed 17/07/19	Thu 01/08/19	Thu 01/08/19	Build Task		100%
601875	Wed 17/07/19	Thu 01/08/19	Thu 01/08/19	Wed 17/07/19	Thu 01/08/19	Thu 01/08/19	Build Task		100%
601875	Mon 10/06/19	Mon 29/07/19		Fri 03/01/20	Mon 03/02/20		Design Task		0%
601875	Fri 03/01/20	Mon 03/02/20		Fri 03/01/20	Mon 03/02/20		SG4 Milestone	2019/2020	0%	SG4: Detailed Design Complete
601875	Mon 06/01/20	Tue 04/02/20		Fri 28/02/20	Mon 30/03/20		Commercial Task		0%
601875	Fri 28/02/20	Mon 30/03/20		Fri 28/02/20	Mon 30/03/20		SG5 Milestone	2019/2020	0%	SG5: Target Cost Achieved
601875	Mon 02/03/20	Tue 31/03/20		Fri 13/03/20	Tue 28/04/20		Mobilisation Task		0%
601875	Mon 16/03/20	Wed 29/04/20		Thu 09/04/20	Wed 27/05/20		Build Task		0%
601875	Thu 09/04/20	Wed 27/05/20		Thu 09/04/20	Wed 27/05/20		SG6 Milestone	2020/2021	0%	SG6: Construction Complete
603767	Mon 04/03/19	Mon 04/03/19	Mon 04/03/19	Fri 26/07/19	Mon 02/09/19				95%
603767	Mon 04/03/19	Mon 04/03/19	Mon 04/03/19	Fri 26/07/19	Mon 17/06/19	Mon 17/06/19	Design Task		100%
603767	Mon 04/03/19	Mon 04/03/19	Mon 04/03/19	Wed 05/06/19	Wed 05/06/19	Wed 05/06/19	Design Task		100%
603767	Thu 23/05/19	Thu 23/05/19	Thu 23/05/19	Mon 01/07/19	Wed 10/07/19	Wed 10/07/19			100%
603767	Wed 05/06/19	Mon 17/06/19	Mon 17/06/19	Wed 05/06/19	Mon 17/06/19	Mon 17/06/19	SG4 Milestone	2019/2020	100%	SG4: Detailed Design Complete
603767	Thu 06/06/19	Thu 20/06/19	Thu 20/06/19	Fri 28/06/19	Wed 24/07/19	Wed 24/07/19	Commercial Task		100%
603767	Fri 28/06/19	Wed 24/07/19	Wed 24/07/19	Fri 28/06/19	Wed 24/07/19	Wed 24/07/19	SG5 Milestone	2019/2020	100%	SG5: Target Cost Achieved
603767	Mon 01/07/19	Mon 15/07/19	Mon 15/07/19	Fri 12/07/19	Fri 26/07/19	Fri 26/07/19	Mobilisation Task		100%
603767	Mon 15/07/19	Mon 29/07/19	Mon 29/07/19	Fri 26/07/19	Mon 02/09/19		Build Task		55%
603767	Fri 26/07/19	Mon 02/09/19		Fri 26/07/19	Mon 02/09/19		SG6 Milestone	2019/2020	0%	SG6: Construction Complete
601874	Thu 15/08/19	Wed 16/10/19		Thu 21/11/19	Tue 04/02/20				0%
601874	Thu 15/08/19	Wed 16/10/19		Thu 10/10/19	Tue 10/12/19		Commercial Task		0%
601874	Thu 10/10/19	Tue 10/12/19		Thu 10/10/19	Tue 10/12/19		SG5 Milestone	2019/2020	0%	SG5: Target Cost Achieved
601874	Fri 11/10/19	Wed 11/12/19		Thu 07/11/19	Tue 21/01/20		Mobilisation Task		0%
601874	Fri 08/11/19	Wed 22/01/20		Thu 21/11/19	Tue 04/02/20		Build Task		0%
601874	Thu 21/11/19	Tue 04/02/20		Thu 21/11/19	Tue 04/02/20		SG6 Milestone	2019/2020	0%	SG6: Construction Complete
601873	Mon 15/07/19	Mon 01/07/19	Mon 01/07/19	Mon 21/10/19	Mon 16/12/19				38%