Wow, I gotta say that it is amazing how this works (mind blown as this becomes more clear). I’m not sure I total understand all that is going on but it is cool. My setup was a bit simpler than your video example so I was able to do it. I did have a slight difference in the numbers for Closed Members (7) and so a few questions. What’s a bit confusing to me is how this inactive code works with Page and Vis. Level Filters.
I set up the date table and then made inactive joins to Account OpenDate & Account CloseDate.
New MBRS MTD = CALCULATE(DISTINCTCOUNT(
USERELATIONSHIP(‘Date Table’[Date], ACCOUNT[OPENDATE]),
First regarding new members, note the last line in the measure above. I have to omit closed members (open & closed in same month). I didn’t think this world work because the closed date is inactive. However, the result is correct. I don’t totally understand, but I’m guessing by using the Acct Closed Date rather than the DateTable date, it works???
Closed MBRS MTD = CALCULATE(DISTINCTCOUNT(ACCOUNT[ACCOUNTNUMBER]),
USERELATIONSHIP(‘Date Table’[Date], ACCOUNT[CLOSEDATE]),
Regarding Closed Members above, the last line is from the original measure and is to make sure all closed members are existing Members (not opened and closed during the current month–March). It would be nice not to ‘hard code’ the date but I’m not sure if that is possible. Again, this seems to be working even though I used the ‘Acct Opendate’ (inactive) rather than the DateTable date.
Thanks for your help on this. I definitely learned something on this one!!