Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Say for example I was looking to find out the number of instances someone took a period of days off sick...
However the sick days are stored per day rather than giving a start and end date.
How can I flag the start of each period of sick days (1 or zero depending on if the start of a sequence of dates)
Current Table
Date | Employee |
24/06/18 | A |
24/06/18 | B |
24/06/18 | C |
25/06/18 | A |
25/06/18 | C |
26/06/18 | A |
26/06/18 | D |
27/06/18 | D |
Desired Table
Date | Employee | Flag (Start of Sequence) |
24/06/18 | A | 1 |
24/06/18 | B | 1 |
24/06/18 | C | 1 |
25/06/18 | A | 0 |
25/06/18 | C | 0 |
26/06/18 | A | 0 |
26/06/18 | D | 1 |
27/06/18 | D | 0 |
As you can see the flag indicates 1 if there are no dates (for corresponding employee) in the table before it... or it indicates a zero if there are dates after, depends which way you want to look at it.
Any help would be really appreciated! Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
You can create a calculated column as below to get it:
Flag (Start of Sequence) =
VAR _preemp =
CALCULATE (
MAX ( 'Table'[Employee] ),
FILTER (
ALL ( 'Table' ),
'Table'[Employee] = EARLIER ( 'Table'[Employee] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
)
)
RETURN
IF ( ISBLANK ( _preemp ), 1, 0 )
Best Regards
Hi @Anonymous ,
You can create a calculated column as below to get it:
Flag (Start of Sequence) =
VAR _preemp =
CALCULATE (
MAX ( 'Table'[Employee] ),
FILTER (
ALL ( 'Table' ),
'Table'[Employee] = EARLIER ( 'Table'[Employee] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
)
)
RETURN
IF ( ISBLANK ( _preemp ), 1, 0 )
Best Regards
@Anonymous I am thinking Cthulhu: Cthulhu - Microsoft Power BI Community. Alternatively, perhaps you get the MAXX of the date with matching criteria (EmployeeFlag) where the date is less than the current row's date. Then you check to see if there is only 1 day between them (just use simple subtraction) and then if that number is greater than 1 it is the start of a new sequence. Otherwise, it is not.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |