Calculate date diff between column date for each row and min slicer date

Question

I think that the following formula summarize pretty well what I want to achieve:

date diff = 
ABS (
    DATEDIFF (
        data_table[login_date],
        SELECTEDVALUE ( 'Date'[Date] ),
        DAY
    )
)

but it returns me the following error

A single value for column 'login_date' in table 'data_table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

In other word I want have a column in my data_table with date diff calculated dynamically based on min slicer date selection.

My final goal is to filter out dynamically users that has not been logged for the last 3 months based on the slicer date range.

Here is the dataset

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

I want user ID to be filtered out when the number of days between the max date of the date range and the day of the last connection is higher than 90 days.

Edit

I'm adding a different formula I'm working on but having few issues to make it work

active users = CALCULATE( DISTINCTCOUNT(data_table[id]), ( FILTER ( VALUES ( data_table[id] ),  DATEDIFF(IF( ISBLANK(SELECTEDVALUE(data_table[login_date])),[Min range date],SELECTEDVALUE(data_table[login_date])),[Max range date],DAY) < 90 ) ))

You can't have a dynamically updated column, it will have to be a measure. Can you post some example data? With expected results? — Jon

Jon Jon · Accepted Answer · 2021-05-05T07:53:15

You can't have a dynamically calculated column, but you can use a measure to do this. The issue that you have with your calculation is that it needed to do a row by row evaluation, rather than over a column. That is why you get an 'A single value for column 'login_date' in table 'data_table' cannot be determined' error.

In this case you can use SUMX, as this is a iterator and it will do row by row. So using the following measures:

Selected Date = SELECTEDVALUE('Calendar'[Date])

This reads the date selected. You can wrap it with a MIN/MAX if needed depending on how your slicer is set up. You can change the slicer to single select, it you just want one value.

Date Calc = SUMX('Table', DATEDIFF('Table'[Login_date], [Selected Date], DAY))

This uses SUMX to calculate on a row by row level.

You can then use this measure to drive your visual. In this example I've filtered out those over 30 days since the login

If you choose a new date, it will recalculate straight away. This should set you on the right path for your use case.

Calculate date diff between column date for each row and min slicer date

1 Answers