Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Completion Item Per Month

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 = ...
Need Help
  • d_gosbell's avatar
    d_gosbell
    4 years ago
    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])
    )

d_gosbell's avatar
d_gosbell
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      d_gosbell
      Super User
      4 years ago
      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's avatar
        Anonymous
        Not applicable
        4 years ago

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