count distinct value filtered based on date diff measure

Question

I have the following dataset

user_id, login_date
111, 01/02/2021
222, 02/15/2021
444, 02/20/2021
555, 01/15/2021
222, 03/10/2021
444, 03/11/2021

I want to count of the number of unique active user_id in the last 90 days based on the max date of my date slicer. I'd like to solve this without using filters. This also needs to be dynamic as max date can be change from the date slicer.

From what I have understand so far I will need to evaluate for each row if the date difference between the current date of the row and the max date of the slicer is less than 90 days. Then for all the rows where the date diff is less than 90 days I will want to count the distinct number of users.

so basically I will have three layer in my final formula

evaluate the date diff.
filter out rows where the date diff is superior to 90 days.
count the distinct users in the remaining rows.

I've tried many approach and formula. I think that this one is closed to something that could work:

Measure test  = CALCULATE(SUMX(DISTINCT(mytable[user_id]),filter(mytable,DATEDIFF(SELECTEDVALUE(mytable[login_date]),[Max range date],DAY)>90)))

this formula return me the following error :

 The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I've also tried applying a if statement to output the date diff as 0 and 1 and hopefully being able to sum this for each unique id with something like this

SUMX( VALUES(my_table[user_id]), IF(DATEDIFF(SELECTEDVALUE(mytable[login_date]),[Max range date],DAY)>90,0,1)

anyway I'm kind of stuck. hopefully my question is clear enough.

AntrikshSharma AntrikshSharma · Accepted Answer · 2021-05-07T07:22:10

You will have to create a disconnected table from which you will use the date column in the slicer, I have prepared a power BI file which included 2 very common scenarios, I hope that helps you.

File - Simon.pbix

Screenshot of the report - https://ibb.co/xjrjVv5

Screenshot of the model - https://ibb.co/Ws1z8D7

DAX Code -

for simon =
IF (
    ISINSCOPE ( simon[Login Date] )
        || ISINSCOPE ( simon[User ID] ),
    VAR LastVisibleDate =
        CALCULATE (
            MAX ( 'Simon Date Table'[Login Date] ),
            ALLSELECTED ( 'Simon Date Table' )
        )
    VAR CurrentDate =
        MAX ( simon[Login Date] )
    VAR TimeJump = 90
    VAR Result =
        CALCULATE (
            DISTINCTCOUNT ( simon[User ID] ),
            simon[Login Date] <= LastVisibleDate,
            simon[Login Date] > LastVisibleDate - TimeJump,
            ALLSELECTED ( simon )
        )
    RETURN
        Result
)

second version:

for simon 2 =
IF (
    ISINSCOPE ( simon[Login Date] )
        || ISINSCOPE ( simon[User ID] ),
    VAR LastVisibleDate =
        CALCULATE (
            MAX ( 'Simon Date Table'[Login Date] ),
            ALLSELECTED ( 'Simon Date Table' )
        )
    VAR CurrentDate =
        MAX ( simon[Login Date] )
    VAR TimeJump = 90
    VAR Result =
        IF (
            CurrentDate <= LastVisibleDate,
            CALCULATE (
                DISTINCTCOUNT ( simon[User ID] ),
                simon[Login Date] <= CurrentDate,
                simon[Login Date] > CurrentDate - TimeJump,
                ALLSELECTED ( simon )
            )
        )
    RETURN
        Result
)

count distinct value filtered based on date diff measure

1 Answers