Date Hierarchy with Filtered Summarize DAX function not as expected

[mike_sjief]

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?

[Anonymous · Accepted solution]

Hi @mike_sjief ,

Consider creating a separate metric to calculate rolling 12-month totals rather than using it directly in an existing metric.
Try modifying the following formula:

Rolling12M_CountOfDistinctContacts =
CALCULATE (
    [CountOfDistinctContacts],
    FILTER (
        ALLSELECTED ( dim_calendar ),
        dim_calendar[d_date]
            IN DATESINPERIOD ( dim_calendar[d_date], MAX ( dim_calendar[d_date] ), -12, MONTH )
    )
)

Best Regards,
Adamk Kong

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

[mike_sjief]

Hi Adamk,

this is indeed what I tried just after I posted it. It worked. Your dax is a bit more elegant so I greatly appreciate your reply.

Kind regards,
Mike