Counting Submission and Closure Trend by Month

[Juliana]

Hi There,

I have a table with the following columns and values:-

ID | SubmissionDate | ClosureDate | OtherColumns

1   | 21-12-2016        | 23-03-2017  |

2   | 12-01-2017        | 01-04-2017  |

Any idea how to create a graph based on the count of submission and closure by different months?

[v-huizhn-msft · Accepted solution]

Hi @Juliana,

You create a Calendar table including all the dates, please click "New Table" under modeling type the following formula on home page. 

Dates=Calendar(MIN(Table[SubmissionDate]),MAX(ClosureDate))

Then create a calculated month column using the formula.

Month=MONTH(Dates[Dates])

Create a measure using the formula.

result=Calculate(
	count(Table[ID]),
		FILTER(Table,
				COUNTROWS(FILTER(VALUES(Dates[Dates]),
						Project[SubmissionDate] <= Dates[Dates] &&
						Project[ClosureDate] >= Dates[Dates] ))
				> 0)
		)

Create a table visual, select the Table[Month] and [result] as value levels.

Best Regards,
Angelia