Forum Discussion

kattlees's avatar
kattlees
Icon for Post Patron rankPost Patron
8 years ago
Solved

Adding sequence column based on time and surgeon

If I have a table that lists surgeon name, OR, Start Time like this:   JONES JANE OR 6 9:00 JONES JANE OR 5 7:45 JONES JANE OR 6 7:00 JONES JANE OR 5 11:45 JONES JANE OR 6 ...
Need Help
  • kattlees's avatar
    kattlees
    8 years ago

    I was able to figure out my own answer by piecing together other answers.

     

    I gave each case of the day a rank by surgeon:

    PreviousRowSales =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Current Month Sales] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Customer] ), Table1[Rank] = Index )
    )

     

    Then got this calculated column:

    PreviousMDOut =
    VAR Index = V_EWS_TASK_RECORD[Rank] - 1
    VAR SDATE = V_EWS_TASK_RECORD[OR Times Final.SCHDT]
    RETURN
    CALCULATE (
    SUM ( V_EWS_TASK_RECORD[MD out] ),
    FILTER ( ALLEXCEPT ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[OR Times Final.SURGEON] ), V_EWS_TASK_RECORD[Rank] = Index && V_EWS_TASK_RECORD[OR Times Final.SCHDT]=SDATE)
    ) 

v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

Hi kattlees,

 

What did you mean date, time? A column in datetime type? Or two columns date and time?

 

Could you please share us some sample data if possible?

 

Thanks,
Xi Jin.

kattlees's avatar
kattlees
Icon for Post Patron rankPost Patron
8 years ago

I was able to figure out my own answer by piecing together other answers.

 

I gave each case of the day a rank by surgeon:

PreviousRowSales =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Current Month Sales] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Customer] ), Table1[Rank] = Index )
    )

 

Then got this calculated column:

PreviousMDOut =
VAR Index = V_EWS_TASK_RECORD[Rank] - 1
VAR SDATE = V_EWS_TASK_RECORD[OR Times Final.SCHDT]
RETURN
CALCULATE (
SUM ( V_EWS_TASK_RECORD[MD out] ),
FILTER ( ALLEXCEPT ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[OR Times Final.SURGEON] ), V_EWS_TASK_RECORD[Rank] = Index && V_EWS_TASK_RECORD[OR Times Final.SCHDT]=SDATE)
) 

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi kattlees,

     

    I'm gald to hear that you have resolved your issue. Please kindly mark your solution as answer. It will help others with relevant issue find the answer more easily.

     

    Thanks,
    Xi Jin.