Categorizing category name based on date range and slicer selection in Power BI

Question

I have the following datasets:

DatasetL

I wanted to write DAX code in which I haven't found the right logics for the following case below: The table of datasets above represent Subject Table with column of "Category" and "Date"

I have 2 slicer which are from Calendar and Comparison Calendar Table where Calendar table have relationship with Subject Table but the Comparison Calendar Table does not have active relationship with Subject Table. hen people select 2 dates (previous and later date) from Calendar and Comparison Calendar Table respectively in the provided slicer, then the DAX will categorize the Category Name into 3 clusters : 1)retired - when the later date does not have the category name from previous date but the previous date does have the category name 2)existing - when both the later date and previous date does have the same category name 3)new - when the later date does have the category name from previous date but the previous date does not have the category name

Below is the screenshot of the data model:

The expected result is per below:

Answer 1

You should first update the relationships to be One-to-Many and Single direction. Should look like below:

Then create a new Measure with:

Cluster = 
  var d1 = CALCULATE( COUNTROWS('Subject'), REMOVEFILTERS('Comparison Calendar') )
  var d2 = CALCULATE( COUNTROWS('Subject'), REMOVEFILTERS('Calendar'), USERELATIONSHIP('Comparison Calendar'[Date], 'Subject'[Date]) )
  return SWITCH( TRUE(),
    d1 > 0 && ISBLANK(d2), "retired",
    d1 > 0 && d2 > 0, "existing",
    ISBLANK(d1) && d2 > 0, "new"
  )

And this should hopefully give the following results: