Sum of distinct count across multiple days

[jofoster]

Here is an example of my dataset:

DaystoMagicMoment     AccountID

    1                                      Account1

    1                                      Account2

   2                                       Account 3

   2                                       Account 4

I want to create a chart that woud show that 2 acccounts match DaytomagicMoment=1 and 4 Accounts when DaytomagicMoment=2. 

I have tried each of these, with no luck.

1) Rolling Totals  on the DistinctCount of AccountID

2) Measure = CALCULATE(DISTINCTCOUNT(Contributions[AccountID]),FILTER(Contributions,Contributions[DaystoMagicMoment]<=Contributions[DaystoMagicMoment]))

3)Measure 2 = SUMX(VALUES(Contributions[DaystoMagicMoment]),CALCULATE(DISTINCTCOUNT(Contributions[AccountID])))

Feels like this should be really easy, but no luck so far. Thanks for any help you can provide

[Zubair_Muhammad]

Hi @jofoster

Try following

Measure =
VAR myDay =
    SELECTEDVALUE ( Contributions[DaystoMagicMoment] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Contributions[AccountID] ),
        Contributions[DaystoMagicMoment] <= myDay
    )

[jofoster]

Thanks for the reply, but still not working for me

I converted your measure to 

TotalAccountsv2 = VAR MyDay = SELECTEDVALUE(Contributions[DaystoMagicMoment]) RETURN CALCULATE(DISTINCTCOUNT(Contributions[AccountID]),Contributions[DaystoMagicMoment]<=MyDay.

This yields the output below, which is not only obviousily incorrect ( i.e.,  each succeeding day should be greater than the day before), but also the Day 1 count is to high when compared to the actual data.

Appreciate your guidance.  thanks

[v-chuncz-msft]

@jofoster,

It seems to work. Show us more information if possible.