Hi @merol ,
I create a table and two columns as you mentioned.
Year = YEAR('Table'[Date])
Column = IF('Table'[YearlyOvertimeHours]>270,"+","-")
Then I calculate the total result and make a new table.
YearlyOvertimeHours =
CALCULATE(
SUM('Table'[Overtime Hours]),
ALLEXCEPT('Table', 'Table'[EmployeeID], 'Table'[Year])
)Table 2 =
SUMMARIZE (
SELECTCOLUMNS (
'Table',
"Year", 'Table'[Year],
"EmployeeID", 'Table'[EmployeeID],
"YearlyOvertimeHours", 'Table'[YearlyOvertimeHours],
"Column", 'Table'[Column]
),
[Year],
[EmployeeID],
[YearlyOvertimeHours],
[Column]
)
Next I create another two calculated columns.
Column 2 =
RANKX (
FILTER (
ALLSELECTED ( 'Table 2' ),
'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] )
&& 'Table 2'[Column] = "-"
),
[Year],
,
ASC
)Column 3 =
VAR _vtable =
ADDCOLUMNS (
ALLSELECTED ( 'Table 2' ),
"_Count",
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table 2' ),
'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] )
&& 'Table 2'[Column 2] = EARLIER ( 'Table 2'[Column 2] )
&& 'Table 2'[Column] = "+"
)
) - 1
)
VAR _vtable2 =
SELECTCOLUMNS (
FILTER (
SUMMARIZE (
_vtable,
'Table 2'[EmployeeID],
'Table 2'[Column],
"_MaxYear", MAX ( 'Table 2'[Year] ),
"_maxx",
MAXX (
FILTER ( _vtable, 'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] ) ),
[_Count]
)
),
'Table 2'[Column] = "+"
),
"_ID", 'Table 2'[EmployeeID],
"_outcome", [_maxx]
)
RETURN
COUNTROWS (
FILTER (
SELECTCOLUMNS (
FILTER (
SUMMARIZE (
'Table 2',
'Table 2'[EmployeeID],
'Table 2'[Column],
"_MaxYear", MAX ( 'Table 2'[Year] )
),
'Table 2'[Column] = "+"
),
"_ID", [EmployeeID],
"_Year", [_MaxYear]
),
[_ID] = 'Table 2'[EmployeeID]
)
)
+ MAXX ( FILTER ( _vtable2, [_ID] = 'Table 2'[EmployeeID] ), [_outcome] )
Finally you can get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please define what you mean by "Repetition" - do you mean overage in consecutive years? If yes then please provide better sample data with gaps. If no then you can use the measure below.
Hi @merol ,
I create a table and two columns as you mentioned.
Year = YEAR('Table'[Date])Column = IF('Table'[YearlyOvertimeHours]>270,"+","-")Then I calculate the total result and make a new table.
YearlyOvertimeHours =
CALCULATE(
SUM('Table'[Overtime Hours]),
ALLEXCEPT('Table', 'Table'[EmployeeID], 'Table'[Year])
)Table 2 =
SUMMARIZE (
SELECTCOLUMNS (
'Table',
"Year", 'Table'[Year],
"EmployeeID", 'Table'[EmployeeID],
"YearlyOvertimeHours", 'Table'[YearlyOvertimeHours],
"Column", 'Table'[Column]
),
[Year],
[EmployeeID],
[YearlyOvertimeHours],
[Column]
)Next I create another two calculated columns.
Column 2 =
RANKX (
FILTER (
ALLSELECTED ( 'Table 2' ),
'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] )
&& 'Table 2'[Column] = "-"
),
[Year],
,
ASC
)Column 3 =
VAR _vtable =
ADDCOLUMNS (
ALLSELECTED ( 'Table 2' ),
"_Count",
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table 2' ),
'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] )
&& 'Table 2'[Column 2] = EARLIER ( 'Table 2'[Column 2] )
&& 'Table 2'[Column] = "+"
)
) - 1
)
VAR _vtable2 =
SELECTCOLUMNS (
FILTER (
SUMMARIZE (
_vtable,
'Table 2'[EmployeeID],
'Table 2'[Column],
"_MaxYear", MAX ( 'Table 2'[Year] ),
"_maxx",
MAXX (
FILTER ( _vtable, 'Table 2'[EmployeeID] = EARLIER ( 'Table 2'[EmployeeID] ) ),
[_Count]
)
),
'Table 2'[Column] = "+"
),
"_ID", 'Table 2'[EmployeeID],
"_outcome", [_maxx]
)
RETURN
COUNTROWS (
FILTER (
SELECTCOLUMNS (
FILTER (
SUMMARIZE (
'Table 2',
'Table 2'[EmployeeID],
'Table 2'[Column],
"_MaxYear", MAX ( 'Table 2'[Year] )
),
'Table 2'[Column] = "+"
),
"_ID", [EmployeeID],
"_Year", [_MaxYear]
),
[_ID] = 'Table 2'[EmployeeID]
)
)
+ MAXX ( FILTER ( _vtable2, [_ID] = 'Table 2'[EmployeeID] ), [_outcome] )
Finally you can get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
