Clustered Chart with 2 different date columns and null values

[Anonymous]

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?

[v-kelly-msft]

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]

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

[v-kelly-msft]

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!

[Anonymous]

@v-kelly-msft 

The open count is not matching for other than March. ended up creating 2 different charts for the x-axis with the created date and Close Date.

[v-kelly-msft]

Hi @Anonymous ，

Let's put original data with output together to check this issue:

Here I see the data in September,the total count is 2,is it right？

Best Regards,
Kelly

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

[Anonymous]

@v-kelly-msft I don't have whole data here, and you're looking at September Release Date not Create Date?

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

[v-kelly-msft · Accepted solution]

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!

[Anonymous]

Thank you this is working now!

[v-kelly-msft]

Hi @Anonymous ，

Glad to help.

Best Regards,
Kelly

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mahoneypat]

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]

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]

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

[Anonymous]

I changed the Data Type = Date with just mm/dd/yyyy and also unchecked the Autotime check box but still no luck.