Index Ranking user with time

[Aeolus]

Hi Community,

I hope you can help me with the following: I want to calculate the time duration of a specific process by a specific user (gebruiker) but have not got further than calculating the time difference between the next process entry.

Current setup:

Start Time = VAR Previous = if(Table[GR Index Total]>1;Table[GR Index Total]-1;0) RETURN calculate(max(Table[datum_tijd]);filter(Table;Table[GR Index Total]=Previous))

" the next ret_date entry from the user when they perform a 'goods receipt' action"

 End time = ret_date

"the end time of the user initiated 'goods receipt' action"

GR Index Total = IF(Table[Process Description]="Goods Receipt";RANKX(FILTER(Table;Table[Process Text]="process_receipt");Table[ID];Table[ID];ASC);0)

"Indexing the goods receipt entries (there are other processes) in order based on ID"

What should happen: 

ID 1291284, user "chefra", process description "Goods Receipt" should have an end time equal to ret_date and a start time equal to ret_date used in ID 1291174 (13:22:10) = time difference of 27 minutes instead of the 19 currently calculated.

Result should be a proper bell curve for all users doing goods receipt instead of what i get now (see example)

[v-eachen-msft · Accepted solution]

Hi @Aeolus ,

You could update your DAX :

GR Index Total =
IF (
    Table[Process Description] = "Goods Receipt";
    RANKX (
        FILTER (
            Table;
            Table[Process Text] = "process_receipt"
                && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
        );
        Table[ID];
        Table[ID];
        ASC
    );
    0
)

Start Time =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[ret_dat]; 1 );
    FILTER (
        'Table';
        'Table'[GR Index Total]
            = EARLIER ( 'Table'[GR Index Total] ) - 1
            && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
    )
)

[Aeolus]

Hi V-eachen,

Works like a charm!!

Thank you,

Aeolus