Count Completion Item Per Month

[Anonymous]

I have created a calendar auto table and trying to do a stacked chart to show open vs closed items per month. I could not return the count completed item per month? 

CountClosedItemPerMonth =

VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX([Table],

VAR ItemEndDate = [Completion Date]

RETURN IF(ItemEndDate >= selectedDate,1,0)

)

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[d_gosbell]

So the expression you have used will count rows that are on or after the last day of the selected period. To count rows with a closed date in the selected month you would normally either create a relationship to the closed date, then you could use a simple countrows expression. Or you could do something like the following pattern

CountClosedItemPerMonth =

VAR startOfPeriod = MIN('Date'[Date])
VAR endOfPeriod = MAX('Date'[Date])

RETURN

CALCULATE( COUNTROWS('Table'),

    'table'[Completion Date] >= startOfPeriod,

    'table'[Completion Date] <= endOfPeriod

)

[Anonymous]

Sorry, I got one problem. it is showing a high value of the blank completion item. How do I resolve it? 

I'm using the following formula:

CountClosedItemPerMonth = CALCULATE(countrows(TABLE),USERELATIONSHIP(TABLE[Completion Date],'Date'[Date]))

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

Sorry, I got one problem. it is showing a high value of the blank completion item. How do I resolve it? 

---

I'm not sure what you mean by "high value" - are you possibly saying you do not want to count rows with no completion date? If so you could just add this condition to the calculate expression.

eg.

CountClosedItemPerMonth = 
  CALCULATE(countrows(TABLE),
  NOT( ISBLANK( TABLE[Completion Date] ) )
  USERELATIONSHIP(TABLE[Completion Date],'Date'[Date])
)

[Anonymous]

This is perfect! Exactly what I want. Thank you very much!