Ok there is alot going on here. let's start by looking at your data model.
The fact your data is coming from 2 seperate data sources in itself isn't a issue. what's of interest is how you are associating the two data sets. you don't describe how they are linked.
The next thing is check if your formula is working. best way to check is load your data and display the columns in a table to see if your formula is resolving correctly. only when you know this do you go onto the final step of using it in your count measure. I'm not sure what counting the number of cancelled things by cancelled date gives you insight wise but it's your requirement as I read it.
I would have thought number of cancelled memberships would be more interesting in which case count of the membership ID would be more suitable but you don't mention this field in your data set I'm just assuming.
Hope this helps more you on.