Cumulative line of number of items on day

[briodan]

I need to create a graph that shows cumulative completed vs target tasks

my data looks like this (including tasks that dont have target dates)

Item	complete target	complete date
task 1	20-Jul	        18-Jul
task 2	20-Jul	        21-Jul
task 3	25-Jul	        25-Jul
task 4	30-Jul	        31-Jul
task 5		
task 6		

what i'm trying to get is a graph that looks like this, where the Y axis is the number of tasks on the particular day, i have looked at some examples of cumulative lines but most of them assume there is a number in the item column that they can sum up vs a string value, and i just can't wrap my head around what to change.

Also key is that task with no date value should not be calculated in the graph.

[v-danhe-msft · Accepted solution]

Hi @briodan

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem like the picture below:

2.Create a new table, two calculated columns, two measures.

Table = CALENDAR("18/6/2018","8/1/2018")

Calculated columns:

Plan Count = CALCULATE(COUNT(Table1[Item]),FILTER('Table1','Table1'[complete target]='Table'[Date]))

Actual Count = CALCULATE(COUNT(Table1[Item]),FILTER('Table1','Table1'[complete date]='Table'[Date]))

Measures: Plan Measure = CALCULATE(SUM('Table'[Plan Count]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

Actual Measure = CALCULATE(SUM('Table'[Actual Count]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

3.Create a Line visual and add the related fields, you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/502lvy080guxk6y/Cumulative%20line%20of%20number%20of%20items%20on%20day.pb...

Regards,

Daniel He