Forum Discussion

Babycakes's avatar
Babycakes
Helper II
2 years ago
Solved

Function to extract column data

Hi There,   We  have a data set that I am trying to create a formula for;   Trying to define this one shift but each shift is different buy basic principle applies. Shift starts Monday week 1 at...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Babycakes 

    You can refer to the following solution.

    1.Create a Index column

    Index = var a=ADDCOLUMNS('Table',"DayNum",SWITCH([Day],"Monday",1,"Tuesday",2,"Wednesday",3,"Thursday",4,"Friday",5,"Saturday",6,"Sunday",7))
return RANKX(FILTER(a,[Shift Start]=EARLIER('Table'[Shift Start])),[Day],,ASC)

    2.Then create the following columns

    Sub_Shift Start = IF([Index]=1,[Shift Start],[Sleepover Finish])
    Sub_Shift Finish = IF([Index]=1,[Sleepover Start],[Shift Finish])

    Output

    Best Regards!

    Yolo Zhu

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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Babycakes 

You can refer to the following solution.

1.Create a Index column

Index = var a=ADDCOLUMNS('Table',"DayNum",SWITCH([Day],"Monday",1,"Tuesday",2,"Wednesday",3,"Thursday",4,"Friday",5,"Saturday",6,"Sunday",7))
return RANKX(FILTER(a,[Shift Start]=EARLIER('Table'[Shift Start])),[Day],,ASC)

2.Then create the following columns

Sub_Shift Start = IF([Index]=1,[Shift Start],[Sleepover Finish])
Sub_Shift Finish = IF([Index]=1,[Sleepover Start],[Shift Finish])

Output

Best Regards!

Yolo Zhu

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