Calculate running total of unique values

[nironixon]

Hi there,
I have the need to calculate how many "active" users we have over time, the running total over unique users (column "UserId").
When a new object is created we are writing the date in column "CreatedDate". When a user deletes his object we are writing the date
in column "DeletedDate" for the given user and object:

Table "Objects":

ID | UserID | CreatedDate | DeletedDate |
1  | "A"    | 2019-01-01  | 2019-01-02  |
2  | "B"    | 2019-01-01  | NULL        |
3  | "B"    | 2019-01-04  | NULL        |
4  | "C"    | 2019-01-04  | NULL        |
5  | "D"    | 2019-01-04  | NULL        |

The table "Objects" is linked to a datetable that are used to control date filter.

Running Total should be;

Date:       Count:
2019-01-01  2
2019-01-02  1
2019-01-03  1
2019-01-04  3

Can anyone help me figuring out how to do a running total calculation on unique UserId values with the given datastructure were
we are using to columns with dates that controls if an object is live or deleted?
In pseudo code I think it is something like;

COUNT DISTINCT ( Objects[UserID];
   FILTER(
     // Only do the calculation on objects less or equal the current date:
     Objects[CreatedDate] <= MAX(DateTable[Date] 
     && 
     // Only count objects thas isnt deleted OR objects where the delete date is in the future
     // relative to current filtercontext date:
     (
       Objects[DeletedDate] = BLANK() 
       ||
       Objects[DeletedDate] > MAX(DateTable[Date] 
    )
)

Regards Niclas

[Anonymous]

Totals=
VAR thisDate=selectedvalue(DateTable[Date])

RETURN
DISTINCTCOUNT(
      FILTER(Objects;
     Objects[createdDate]<=thisDate &&
     ISBLANK(Objects[DeletedDate]);
Objects[UserId])

Once a date is selected, this will return distinct count of users where Deleted is blank and the CreatedDate is less or equal the selected date.
I'm not 100% sure that this works, but I think your friend is the SELECTEDVALUE function.

---

[Anonymous]

Actually, my formula is wrong. You need to exit the filter context of the date, so should be somehing like

Totals=
VAR thisDate=selectedvalue(DateTable[Date])

RETURN
CALCULATE(DISTINCTCOUNT(
      FILTER(Objects;
     Objects[createdDate]<=thisDate &&
     ISBLANK(Objects[DeletedDate]);
Objects[UserId]);ALL(Objects))

The ALL should remove the filter context on Objects.

BUT I would do this with a calculated column rather than a measure...

[nironixon]

Hi Adetogni and thanks for efforts in helping me!!
Unfortunatley I cant get your measure to work, are all parentheses and semicolons in the right place?

Is it possible for you to revisit your answear and see if its ok?
Found a good online formatter here: 
https://www.daxformatter.com/

Best regard Niclas

[Anonymous]

Totals=
VAR thisDate=selectedvalue(DateTable[Date])

RETURN
CALCULATE(DISTINCTCOUNT(
FILTER(Objects;
Objects[createdDate]<=thisDate &&
ISBLANK(Objects[DeletedDate]))
;Objects[UserId]);ALL(Objects))

Try this.

Again, i've not tested, but should work. I'm in general not that friendly with the ALL function, but in this case should work fine.

[nironixon]

Couldnt get your third version to work either. 
I came up with something like this that seems to do the work:

Running Total Unique Users = 
CALCULATE (
    DISTINCTCOUNT( Objects[UserId] );   
    FILTER(
         ALLSELECTED(Objects); 
         Objects[Objects] <= MAX(Dates[Date]) && 
         (
             Objects[DeletedDate] = BLANK() || Objects[DeletedDate] > MAX(Dates[Date])
         )
    )
)

Cheers Niclas!

[Anonymous]

I'm not sure. MAX will return the max value in a column. Does it work when you select a past date?