Distinct Count Column Based on Another Column and Within a Timeframe

[husseyma]

I am using the below formula successfully for a calculated column that returns a unique count of names associated with each UID

DCount Name =

VAR ThisId = 'Transitions'[UID]

RETURN

    CALCULATE (

        DISTINCTCOUNTNOBLANK('Transitions'[Name] ),

        FILTER ( ALL ( 'Transitions' ),'Transitions'[UID] = ThisId)

    )

The problem is that I would like to somehow modify the formula so it takes into account my date slicers. At present I am getting the same count irrespective of changing my date slicers because the formula does not take date into account. There is a date column within the "Transitions" table and I have a separate DateDim table also.

Ultimately I would like to be able to build a rolling 12 month count of the basis of this data.

Any pointers, greatly appreciated.

Thank you!

[rajendraongole1]

Hi @husseyma - To make your calculated column respond to date slicers, you'll need to modify the formula so that it includes the date context.

DCount Name =
VAR ThisId = 'Transitions'[UID]
VAR SelectedDateRange = CALCULATETABLE(
VALUES('DateDim'[Date]),
ALLSELECTED('DateDim')
)
RETURN
CALCULATE(
DISTINCTCOUNTNOBLANK('Transitions'[Name]),
FILTER(
ALL('Transitions'),
'Transitions'[UID] = ThisId &&
'Transitions'[Date] IN SelectedDateRange
)
)

create a measure instead of a calculated column since measures are more dynamic

Rolling 12M DCount Name =
VAR EndDate = MAX('DateDim'[Date])
VAR StartDate = EDATE(EndDate, -12)
RETURN
CALCULATE(
DISTINCTCOUNTNOBLANK('Transitions'[Name]),
FILTER(
ALL('Transitions'),
'Transitions'[UID] = MAX('Transitions'[UID]) &&
'Transitions'[Date] >= StartDate &&
'Transitions'[Date] <= EndDate
)
)

Hope it works in your scenerio

[husseyma]

Thank you so much for your response, unfortunately the modified formula suggested still returns the same result as the original calculation, ie it ignores my date slicers and displays a count for the entire table.  The Rolling 12M also does not appear to be returning relevant results...