Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
why id 3 was open in feb and march?
Id 3 was completed or closed in April hence it should be counted has open for both the months.
Hi,
Try like this
=num(count({<(Completed date={$(=MAX([Completed date]))},WeekKey=,FinancialYear=,FiscalQuarter=,([Completed date]={'Open'},WKey=>}OPen_Issues),'#,##0;(#,##0)')
Check this out Mahesh