How to DistinctCount between a row context and including two further filters?

[D_PBI]

Hi,
I have a Date table (dimDate). A basic mock-up is below:
dimDate[Date]     dimDate[Year]     dimDate[Month]
31/05/2020          2020                    May
01/06/2020          2020                    June

I have the Customer table. A basic mock-up is below:
Customer[Customer ID]     Customer[Opening Date]     Customer[Last Active]     Customer[Type]
123                                     06/09/2019                           blank                               Block
453                                     08/01/2024                           19/01/2024                     Circle
923                                     11/01/2024                           02/02/2024                     Circle

The dimDate[Date] column is a 1-* relationship to the customer[Opening Date] column.
I have a Matrix visual where the only two row values are the dimDate[Year] and dimDate[Month].

I need to be able to count the distinct Customer[Customer ID] records where the Customer[Opening Date] and the [Customer[Last Active] dates fall within the same Matrix period (row context?).

For example, if the Matrix's dimDate[Year] hasn't been expanded (so we're focusing on counts by dimDate[Year] of say 2019) then I need the DAX to calculate the number of unique Customer[Customer ID] records where that Customer ID has a Customer[Opening Date] within 2019 and the same Customer ID record having a Customer[Last Active] within 2019.

Should the Matrix's dimDate[Year] (i.e. 2019) been expanded to show the dimDate[Month] (so focusing on the month of July for this explanation) then I need to the DAX to calculate the the number of unique Customer[Customer ID] records where that Customer ID has a Customer[Opening Date] within July 2019 and the same Customer ID record having a Customer[Last Active] within July 2019.

As my mock-up dataset above shows, there may be Customer[Customer ID] records where the Customer[Opening Date] and Customer[Last Active] don't fall within the same period (i.e. either dimDate(Year] and dimDate[Month]) and so these should not be counted.
If grouping by dimDate[Year] only (so the dimDate[Month] hasn't been expanded), then you may have a Customer[Customer ID] record that has a Customer[Opening Date] of 05/09/2022 and a Customer[Last Active] of 12/12/2022. If so this would be included in the dimDate[Year] Matix row context as it's the same dimDate[Year], but wouldn't be included if the Matrix has been expanded to show the dimDate[Month] row context.

To add, there is another filter clause that needs to be present and that is simply Customer[Type] = "Block".

I hope this makes sense. Obviously, the same calculation needs to perform for each year and month.
How do I achieve this?
Thanks.

[Anonymous · Accepted solution]

Hi @D_PBI ,

Here I create a sample to have a test.

Please try this code to create a measure.

Measure = 
CALCULATE (
    COUNT ( Customer[Customer ID] ),
    FILTER (
        Customer,
        Customer[Last Active] <> BLANK ()
            && Customer[Opening Date] >= MIN ( DimDate[Date] )
            && Customer[Last Active] <= MAX ( DimDate[Date] )
            && Customer[Type] = "Block"
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

if you compare scalar values to ranges you cannot use a data model. Instead use disconnected tables, INTERSECT and measures.