Hi all,
I am trying to create a calculated column to return a distinct count of "ID" based on two filter conditions:
1) "Person ID" is equal to "Person ID" of current row.
2) "Start Date" is within L12M from "Start Date" of current row.
I've got 1) to work, but cannot get 2) to work. Here's my DAX which achieves 1):
I have tried the below to get 2) to work but I get an error of a table being supplied where a single value is expected:
Can anyone help?
Thank you,
I am trying to create a calculated column to return a distinct count of "ID" based on two filter conditions:
1) "Person ID" is equal to "Person ID" of current row.
2) "Start Date" is within L12M from "Start Date" of current row.
I've got 1) to work, but cannot get 2) to work. Here's my DAX which achieves 1):
CALCULATE(
DISTINCTCOUNT('Table'[ID]),
FILTER(ALL('Table'), 'Table'[Person ID] = EARLIER('Table'[Person ID]))
)
I have tried the below to get 2) to work but I get an error of a table being supplied where a single value is expected:
CALCULATE(
DISTINCTCOUNT('Table'[ID]),
FILTER(ALL('Table'), 'Table'[Person ID] = EARLIER('Table'[Person ID])),
FILTER(ALL('Table'), 'Table'[Start Date] >= DATESINPERIOD('Table'[Start Date], EARLIER('Table'[Start Date]), -12, MONTH))
)
Can anyone help?
Thank you,