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 =
VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX([Table],
VAR ItemEndDate = [Completion Date]

RETURN IF(ItemEndDate >= selectedDate,1,0)
)
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])
    )

5 Replies

  • 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])
        )