Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |