You are not utilizing this correctly from what I can tell.
I recreated the data set you posted above, and was able to return a distinct count of 1 within a KPI visual utilizing the method I recommended.
Here is my result
Here is the calculated field I am filtering on:
And here is my dataset:
This will still work even if you are using a calculated field to aggregate UserID like this:
distinct_count(UserID)
and then placing that in the values field well. It will still return 1.
From my experience, the order of operations is very important to understand in order to be successful when building these visualizations.
The date filter applied on the visual will filter out the dates wanting to be excluded in the Sum. So instead of us wanting to see “Life Time Purchases” we are returning all Purchases made between 2023/01/01 and 2023/03/01 for each user, and then filtering out the users who don’t meet the threshold.
If you change the PRE_AGG to PRE_FILTER, the date filter won’t have an effect.