MDX formula for Running SUM of distinct accounts over time?

Question

I’m trying to calculate batch penetration or contact rate over time and having trouble creating a calculated member that would allow me to show distinct count of accounts contacted over time. I’m relatively inexperience with MDX and was hoping to get some guidance.

Here is the simplified model
Account Dimension – AcctID. I’m also using this as a fact in the cube by using a count of rows. This is a distinct count of accounts
Activity Dimension – Period (YYYYMM), Activity Date (key)– dimension type is time.
Batch Dimension – BatchID, BatchPeriod (YYYYMM) – ex: 202207
FactActivity – Acctid, ActivityDate, BatchID, NbrContacts Ex: 1, 09/01/2022, 123, 100
FactBatch – Batchid, NbrAccounts – ex: 123, 1,000
FactAccount – DistinctAccounts using the account dimension.

Of the 1000 accounts (FactBAtch.NbrAccounts) that are part of period 202207, I want to calculate the % of the batch that was contacted each month, but not count duplicate contacts. If an account was contacted 5 times in July and 2 in august, it still counts as 1. My thought is to create a cumulative distinct count over time that could be used to divide by the nbraccounts to get the penetration rate.

Here is what I have so far. nbtrAccounts works great and shows the distinct number of accounts contacted each period. The cumulative metric is wrong for each period as it is summing distinct accounts by period, but also counting account that have been contact from prior periods.

Activity Period NbrAccounts Distinct Account Cumul Distinct Accts Penetration Rate
202207 1,000 50 50 (correct) 5% (correct)
202208 1,000 100 150 (sb 100, 50 where part of 202207) 15% (incorrect – sb 10%)
202209 1,000 300 300 (sb 200, 200 contacted in prior periods) 30% (incorrect – sb 20%)
1000 200 (correct) 50% incorrect - sb 20%

Here are my Formulas for the Cumulative Distinct accounts over activity periods and Pentation Rate. While the total is correct (200 - confirmed with a sql query on my fact table) the individual period rollups are wrong, the individual periods are incorrect and my rates are wrong. If someone has see this same problem, I'd appreciate the guidance. Thanks!

Cumul Distinct account calc member.
COUNT(
NONEMPTY(
NONEMPTY(
[Acct].[Acct ID].MEMBERS
, [Distinct Accounts])
, {[Distinct Accounts]}
* {NULL : [Activity Period].[Activity Period].CURRENTMEMBER}
)
)

Penetraction Rate formula
iif([Measures].[NbrAccounts]>0 , [Measures].[Cumu Distinct Accts] / VALIDMEASURE([Measures].[NbrAccounts]),NULL)

Answer 1

From your description, what you need is not a running SUM, but a running TOTAL of your distinct count.
Looking at the elements of your cube that you've listed so far, the general logic of applying the running total time intelligence calculation to the distinct count could be
Aggregate ( NULL : [Activity Period].[Activity Period].CURRENTMEMBER, [Measures].[DistinctAccounts] ).
This would of course require that your dimensions and measure groups correctly model your data, and in particular that you have correctly populated the dimension usage matrix, notably the Activity - FactAccount relationship.