Forum Discussion

BenBrubacher's avatar
BenBrubacher
Frequent Visitor
5 years ago
Solved

Calculating Next Row

I need help to calculate how long it takes an agent to handle each page during their call, and I need to calculate the value from the next row.   I have the following transactional table which reco...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi BenBrubacher ,

     

    Based on your description, I think creating an index column based on page number in the query editor will make things easier.

    Then use this index column and the ALL function to get the result.

    PET = VAR _INDEX = SELECTEDVALUE('Table'[Index])
VAR _PET = CALCULATE(MAX('Table'[PST]),FILTER(ALL('Table'),'Table'[Index]=_INDEX+1))
RETURN IF(ISBLANK(_PET),MAX('Table'[CET]),_PET)
    D = VAR _PET = [PET] 
VAR _PST = MAX('Table'[PST])
RETURN (_PET-_PST)*86400

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BenBrubacher's avatar
BenBrubacher
Frequent Visitor
5 years ago

Images didnt post properly, heres the current dataset

 

Here's what I want it to look like?

V-lianl-msft's avatar
V-lianl-msft
Community Support
5 years ago

Hi BenBrubacher ,

 

Based on your description, I think creating an index column based on page number in the query editor will make things easier.

Then use this index column and the ALL function to get the result.

PET = VAR _INDEX = SELECTEDVALUE('Table'[Index])
VAR _PET = CALCULATE(MAX('Table'[PST]),FILTER(ALL('Table'),'Table'[Index]=_INDEX+1))
RETURN IF(ISBLANK(_PET),MAX('Table'[CET]),_PET)
D = VAR _PET = [PET] 
VAR _PST = MAX('Table'[PST])
RETURN (_PET-_PST)*86400

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.