Cumulative Total on Line Chart

[Anonymous]

Hello, 

Once again I am stuck with PowerBI and need some help. 

I have some calculated columns that return a value (number 1) if certain conditions in other colums  are true:

SG4 Baseline = IF(SW_Master_Programme_CSV[MS Type]="SG4: Detailed Design Complete" && SW_Master_Programme_CSV[MS Baseline Year]="2019/2020" && SW_Master_Programme_CSV[Date Type]="Baseline Start",1,0)

SG4 Complete = IF(SW_Master_Programme_CSV[MS Type]="SG4: Detailed Design Complete" && SW_Master_Programme_CSV[MS Baseline Year]="2019/2020" && SW_Master_Programme_CSV[Date Type]="Actual Finish" && SW_Master_Programme_CSV[Percent Complete As Text]="1",1,0)

I have then have two measures: 

SUM SG4 Baseline = SUM(SW_Master_Programme_CSV[SG4 Baseline])

SUM SG4 Baseline = SUM(SW_Master_Programme_CSV[SG4 Baseline])

I am plotting the two measures on a line chart. The Axis is 'MonthNameShort' which is linked to a date table etc. 

In my date table I've made a fiscal year column and sorted 'MonthNameShort' by that. 

The result I am getting is as follows:

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. 

Any help much appreciated

[Anonymous]

I have tried the following two measures

XXXX =
CALCULATE(
SUM(SW_Master_Programme_CSV[SG4 Baseline]),
FIlter(
ALLSELECTED(SW_Master_Programme_CSV),
SW_Master_Programme_CSV[Date]<= MAX(SW_Master_Programme_CSV[Date])
)
)

XXXX =
CALCULATE(
SUM(SW_Master_Programme_CSV[SG4 Baseline]),
FIlter(
ALLSELECTED(SW_Master_Programme_CSV),
SW_Master_Programme_CSV[Date]<= MAX(SW_Master_Programme_CSV[Date])
)

I then plotted them on a line chart

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.

Any explanation much appreciated 

[v-piga-msft]

Hi @Anonymous ,

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

Cherry

[Anonymous]

@v-piga-msft  thank you for your response

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)

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:

1. The cumulative number of SG4 milestones each month according to baseline start dates

2. The cumulative number of SG4's that are complete each month according to actual finish dates

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?

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%

[Anonymous]

If i remove the slicer I get the below result, the number of measures seems to have no effect:

Is it something to do with my axis? Can the cumulative formula not plot the cumulative totals over that field?

[Anonymous]

@v-piga-msft - Any ideas from my comments above?