Help needed- Distinct count with filter and multiple dates

Hello jgarcin8
i think you need Distinct count of [user_id] base on [acknowledgement_status] not [completion_date] and [distribution_date]. but you need in Count of [user_id] base on  [completion_date] and [distribution_date].Please guide further.

Distinct count based on completion_date =
CALCULATE (
DISTINCTCOUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
####################################################
count based on completion_date =
CALCULATE (
COUNT ( 'Distinct Count'[distribution_date] ),
ALLEXCEPT ( 'Distinct Count', 'Distinct Count'[completion_date] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
###################################################

count based on distribution_date =
CALCULATE (
COUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "All"
)

jgarcin8 I hope this help you.
 

Hi jgarcin8 ,

I suggest you to add two columns to convert distribution date and completion date from datetime to date format.

distribution only date = DATEVALUE('Table'[distribution_date]) 

completion only date = DATEVALUE('Table'[completion_date])

Then create a calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter",
        "Q" & ""
            & QUARTER ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] )
)

Create two inactive relationship between tables.

Measure:

Measure1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[completion only date] ),
    'Table'[acknowledgement_status] = "Complete"
)

Measure2 = 
CALCULATE (
    COUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[completion only date] ),
    'Table'[acknowledgement_status] = "Complete"
)

Measure3 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[distribution only date] ),
    USERELATIONSHIP ( 'Table'[distribution only date], 'Calendar'[Date] ),
    'Table'[acknowledgement_status] = "All"
)

Result is as below.

Best Regards,
Rico Zhou

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