Track Progression of Employees First to X Weeks Hours Worked

[Qotsa]

Hi,

Goal is to show employees hrs worked from the first week where they have worked onwards.

Desired Output Matrix Column Headings are the 1st, 2nd instance etc that they worked in a given Week.

Every Employee has a 1st Wk Hrs Worked value but may not have values subsequently.

I have this measure that gives a running total of the number of weeks in which they have worked.

Weeks Worked =

SUMX(

    FILTER(

        SUMMARIZE(        

            'Data_Table',

          'Data_Table',[Employee_id],

            'Data Table'[Week]

       ) ,

        'Data_Table[Employee_id] <> BLANK()

    )

    ,1

)

but struggling to get to my desired output.

Data -> https://1drv.ms/x/s!AiX0_cACeSxJgSw4iLbtYaaP5nWt?e=vFYNb2

[Qotsa]

@johnt75 That works fantastically well. TY.

[johnt75 · Accepted solution]

You can use the new INDEX function, but you need to make sure that the ORDERBY column is consistent - some of the values have a space between the Wk and the number, some don't.

First week = 
CALCULATE(
	SUM( 'Table'[Hrs Worked]),
	INDEX( 1,, ORDERBY( 'Table'[Week & Year] ), KEEP, PARTITIONBY( 'Table'[Employee_id] ) )
)

You would need to replicate this measure for subsequent weeks, just change the 1 to 2, 3 etc. Or you could put it into  a calculation group.