Date Hierarchy with Filtered Summarize DAX function not as expected

Hi you all,

I'm hoping you can help me out on something. I've created a dax measure which gives me a distinct count of a contact column on two filters, one which is a column filter value (contribution status) , another is the count of a column of a summarize function (the contact should have at least 2 contribution_bk).

Ideally I want to have a rolling 12 month view of this measure with a year-quarter hierarchy The expected behaviour and results should be :

• The value on 2023-Q4 should show me the distinct count of a column with the set filters for the last 12 months , so from 01-01-2023 to 31-12-2023
• 2023-Q3 should show me the distinct count of a column with the set filters for the last 12 months , so from 01-10-2023 to 30-09-2023

The dax measure:

CountOfDistinctContacts =

CALCULATE (

    DISTINCTCOUNT ( 'fct_contributions'[contact_pk] ),

    FILTER (

        SUMMARIZE (

            fct_contributions,

            'fct_contributions'[contact_pk],

            "ContributionCount", COUNTROWS ( 'fct_contributions' )

        ),

        [ContributionCount] >= 2 &&

        CALCULATE (

            COUNTROWS ( 'fct_contributions' ),

            'fct_contributions'[contribution_status_fk] = 1,

            DATESINPERIOD(dim_calendar[d_date],MAX(dim_calendar[d_date]), -12, MONTH)

        ) >= 2

    )

)

in the star model, the fct_contribution has a relationship with the dim_calendar table.

Here the total is correct, meaning the dax measure correctly takes summarizes the table taking only columns which have a date between 01-01-2023 and 31-12-2023. And I was expecting the 30.585 value to appear on 2023 Qtr 4, but it doesn't..

If I take away the Year and Quarter from the table, it shows me the correct value (30.585).

 

What am I missing?