Return True depending on Date Frequency of Groups

[Dominok123]

Hey,

So I'm trying to figure out how to create the third column 'Infrequent' as seen in the table below. What I need this column to return is whether or not the same ID has had multiple records but 6+ months apart. If the records are 6+ months apart, the Infrequent column would return 1, otherwise 0. However, as you can see with ID #3 in the below table, one record is actually 6+months apart from the other rows, however this also would be 0 as the other two records are not 6months apart. I only want to identify ID's that have never had two records within 6 months of each other.

Any help would be much appreciated.

Table1:

ID	Date	Infrequent
1	01/01/2019	0
1	03/02/2019	0
2	16/01/2018	1
2	20/10/2018	1
3	17/03/2018	0
3	02/06/2018	0
3	31/03/2019	0
4	05/06/2017	1
4	01/03/2019	1

[AlB]

Hi @Dominok123 

Try this for your new calculated column, where Table1 is the table you show:

NewColumn =
VAR _AuxTable =
    ADDCOLUMNS (
        CALCULATETABLE ( DISTINCT ( Table1[Date] ); ALLEXCEPT ( Table1; Table1[ID] ) );
        "_Dif";
        VAR _PreviousDate =
            CALCULATE ( MAX ( Table1[Date] ); Table1[Date] < EARLIER ( Table1[Date] ) )
        RETURN
            IF (
                NOT ISBLANK ( _PreviousDate )
                    && DATEDIFF ( _PreviousDate; [Date]; MONTH ) <= 6;
                1;
                0
            )
    )
RETURN
    IF ( SUMX ( _AuxTable; [_Dif] ) = 0; 1; 0 )

See it at work in the attached file

[Dominok123]

@AlB Thats a pretty good start, appreciate the effort!

Testing it out on my real dataset its picking up ID's which have multiple instances of the same date. So ID #30 with two rows of Date: 30/07/2018 is placed as '1'. It also allocates '1' to every row that only has 1 ID. So if theres only one row with ID #60 then that is placed as '1'.

My bad, should have made better dummy data to account for these things.