How to calculate a sequence?

Hello everyone! Just recently I tried to use Power BI again after a year of delay and I stuck with a rare issue. I need to calculate a sequence of hired people. There are 3 columns Week ...

Need Help

v-yulgu-msft

9 years ago

Hi Mer333,

To achieve your goal, only need to add some calculated columns to source table (in my test, it's named as Table1), please refer to below DAX formulas:

Previous Name for same ID =
LOOKUPVALUE (
    Table1[Name],
    Table1[Position ID], Table1[Position ID],
    Table1[Week], Table1[Week] - 1
)

flag =
IF ( Table1[Previous Name for same ID] = Table1[Name], 0, 1 )

Number in sequence =
CALCULATE (
    SUM ( Table1[flag] ),
    ALLEXCEPT ( Table1, Table1[Position ID] ),
    Table1[Week] <= EARLIER ( Table1[Week] )
)

Result output.