Forum Discussion
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...
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,
KellyDid I answer your question? Mark my raeply as a solution!
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
Community Support
Community SupportHi 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!
Thank you this is working now!
- v-kelly-msft
Hi Anonymous ，
Glad to help.
Best Regards,
KellyDid I answer your question? Mark my raeply as a solution!