Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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.

skuruvatti_0-1630622765977.png

 

 

skuruvatti_0-1630622632226.png

what's wrong?

1 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:

vkellymsft_0-1632106310107.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

14 REPLIES 14
v-kelly-msft
Community Support
Community Support

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
Not applicable

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

skuruvatti_0-1631135013194.png

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
Not applicable

 

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.

 

skuruvatti_1-1631725448291.png

 

Hi @Anonymous ,

 

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

vkellymsft_0-1631757715100.pngvkellymsft_1-1631757742363.png

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
Not applicable

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

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:

vkellymsft_0-1632106310107.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Anonymous
Not applicable

Thank you this is working now!

Hi @Anonymous ,

 

Glad to help.

 

Best Regards,
Kelly

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

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

 
 

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

skuruvatti_0-1630628635636.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

 

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

 

skuruvatti_0-1630689882006.pngskuruvatti_1-1630689898493.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.