Measure Help: bar chart with measures on 2 different date fields

[AnnieV]

I need help with creating a measure for a bar chart.
I want the bar chart to display the number of created and closed complaints in a given month.
My data model has a table called "Complaints" with the fields COMPLAINT_ID, COMPLAINT_CREATION_DATE, and COMPLAINT_CLOSED_DATE. This complaints table is linked to my "Calendar" table with the field COMPLAINT_CREATION_DATE = DATE field of the Calendar table.

Because of the link between COMPLAINT_CREATION_DATE = DATE the measure for created complaints was easy (Count(DISTINCT COMPLAINT_ID)) 

How can I calculate the number of closed complaints in a month?



Data examples:

Complaints:

COMPLAINT_ID	COMPLAINT_CREATION_DATE	COMPLAINT_CLOSED_DATE
COMPLAINT_1	01-01-2023	15-02-2023

Calendar:

DATE	Year	Month
01-01-2023	2023	1

[ajaykakkar93 · Accepted solution]

hi,
Please check this out: Island Calendar