Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Clustered Chart with 2 different date columns and null values

Need Help!

 

I have a table with Create Date and Close date with States(Open/Close), am looking for a clustered chart that shows both Open and Closed Tickets by Month.

 

Created Measure:

1. Open Count =

CALCULATE(
COUNTROWS('Pending Business Review'),
'Pending Business Review'[State (groups)] = "Open"
)
2. Closed Count =
CALCULATE(
COUNTROWS('Pending Business Review'),
'Pending Business Review'[State (groups)] = "Closed",
USERELATIONSHIP('Pending Business Review'[PBI Release Date],Dates[Date])
)

 

used Date table as a relationship but somehow all months are not visible.

 

 

what's wrong?

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    Try below measures:

    Open Count =
CALCULATE (
    COUNTROWS ( 'Pending Business Review' ),
    FILTER (
        ALL ( 'Pending Business Review' ),
        (
            MONTH ( 'Pending Business Review'[Created Date] )
                = MONTH ( MAX ( 'Dates'[Date] ) )
                || MONTH ( 'Pending Business Review'[PBI Release Date] )
                    = MONTH ( MAX ( 'Dates'[Date] ) )
        )
            && 'Pending Business Review'[State(groups)] = "Open"
    )
)

    Closed Count =
CALCULATE (
    COUNTROWS ( 'Pending Business Review' ),
    FILTER (
        ALL ( 'Pending Business Review' ),
        (
            MONTH ( 'Pending Business Review'[Created Date] )
                = MONTH ( MAX ( 'Dates'[Date] ) )
                || MONTH ( 'Pending Business Review'[PBI Release Date] )
                    = MONTH ( MAX ( 'Dates'[Date] ) )
        )
            && 'Pending Business Review'[State(groups)] = "Closed"
    )
)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my raeply as a solution!

14 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Make sure your Dates[Date] and both of your CreatedDate and ReleaseDate are all type Date (not DateTime).  Also, it looks like you have Auto Date/Time enabled in the options.  It probably isn't the cause of your issue but it is better practice to turn that off and use a Date table like the one you have.

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago
       
       

      I do have the format as mm/dd/yyyy and am using hierarchy because other reports using the date/month and don't want to use the relationship with other tables in same.pbix

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        5 years ago

        From your pics, I see that your Date columns are type DateTime (see Data Type column).  If there is a time component hidden by the format that don't match, you will not get correct filtering and results.  Is the third Date column also a DateTime?

         

        Pat

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ,

     

    What's the relationship between Dates and Pending business Review?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      this is the relationship with Date to Pending Business Review - Created Date and PBI Release Date

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  Anonymous ,

         

        Sorry for the late reply.

        The relationship is fine.Based on my understanding,the output seems correct,what is your expected result?

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!