Open items for perticular month

[#1]

Any one have any other way to archive this? by doing this in way suggested by  Rupas , the  link to fact table will be based to created date but in this has to be linked to ID

I trying calculate count of total open items at end of the month based on completed date below table gives the example of what i am trying to get as output. Can someone please help me on this.

ID	Created date	Completed date
1	1/30/2014	1/31/2014
2	1/31/2014	2/10/2014	Open in Jan
3	2/1/2014	4/10/2014	open in Feb&March
4	2/15/2014	3/2/2014	open in Feb
5	3/10/2014	3/10/2014
6	3/12/2014	4/2/2014	open in March
7	3/30/2014		open in March&April
8	4/3/2014		Open in April
9	4/3/2014	4/3/2014	Open in April
10	4/5/2014		Open in April
			Required Output
			Jan-14	Feb-14	Mar-14	Apr-14
	Open		1	2	3	3

[alematex]

why id 3 was open in feb and march?

[#3]

Id 3 was completed or closed in April hence it should be counted has open for both the months.

[Anonymous]

Hi,

Try  like this

=num(count({<(Completed date={$(=MAX([Completed date]))},WeekKey=,FinancialYear=,FiscalQuarter=,([Completed date]={'Open'},WKey=>}OPen_Issues),'#,##0;(#,##0)')

[#5 · Accepted solution]

Check this out Mahesh

testMahesh.qvw