Forum Discussion

mike_sjief's avatar
mike_sjief
New Member
2 years ago
Solved

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 (contrib...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
mike_sjief
New Member
2 years ago

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