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.
Hi,
I would like to have the count of consecutive days in a calculated column based on for example sickday.
eks
employee date Sick =1 consecutive days
A 1.1.2020 1 2 (1.1+2.1)
B 1.1.2020 1 3 (1.1+2.1+3.1)
C 1.1.2020 0 0
A 2.1.2020 1 2 (1.1+2.1)
B 2.1.2020 1 3 (1.1+2.1+3.1)
C 2.1.2020 1 2 (2.1+3.1)
A 3.1.2020 0 0
B 3.1.2020 1 3 (1.1+2.1+3.1)
C 3.1. 2020 1 2 (2.1+3.1)
Any help would be grateful!
Solved! Go to Solution.
hi @Anonymous
You could use this formula to get it:
Result =
VAR _date1 =
CALCULATE (
MAX ( 'Table'[date] ),
FILTER (
CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
'Table'[date] <= EARLIER ( 'Table'[date] )
)
)
VAR _date2 =
CALCULATE (
MIN( 'Table'[date] ),
FILTER (
CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
'Table'[date] > EARLIER ( 'Table'[date] )
)
)
VAR _date3=IF( ISBLANK(_date2),CALCULATE (
MAX ( 'Table'[date] ),ALLEXCEPT('Table','Table'[employee] ))+1,_date2)
RETURN
var a= CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT( 'Table','Table'[employee]),
'Table'[date] <_date3&&
'Table'[date] > _date1
)
) + 0
return
if('Table'[Sick]=0,0,a)
Regards,
Lin
hi @Anonymous
You could use this formula to get it:
Result =
VAR _date1 =
CALCULATE (
MAX ( 'Table'[date] ),
FILTER (
CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
'Table'[date] <= EARLIER ( 'Table'[date] )
)
)
VAR _date2 =
CALCULATE (
MIN( 'Table'[date] ),
FILTER (
CALCULATETABLE ( 'Table',ALLEXCEPT('Table','Table'[employee] ),'Table'[Sick]=0),
'Table'[date] > EARLIER ( 'Table'[date] )
)
)
VAR _date3=IF( ISBLANK(_date2),CALCULATE (
MAX ( 'Table'[date] ),ALLEXCEPT('Table','Table'[employee] ))+1,_date2)
RETURN
var a= CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT( 'Table','Table'[employee]),
'Table'[date] <_date3&&
'Table'[date] > _date1
)
) + 0
return
if('Table'[Sick]=0,0,a)
Regards,
Lin
This solved my problem! Thank you!
Proud to be a Super User!
Brilliant!!!!!
@Anonymous
you can try to create a column
Column = if('Table'[Sick]=0,0,sumx(FILTER('Table','Table'[employee]=EARLIER('Table'[employee])),'Table'[Sick]))
Proud to be a Super User!
Tnx for your reply, however it does not give the correct numbers.
@Anonymous , refer if these can help
https://community.powerbi.com/t5/Desktop/Win-Losing-Streak/td-p/273547
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |