Forum Discussion

JanoLehocky's avatar
JanoLehocky
Icon for Helper I rankHelper I
3 years ago
Solved

Duration of Consecutive Days off for unique IDs

Good afternoon Power BI Community,   I've spent a good deal of time today trying to make this work from various other postings but have not had any success.    Specifically I tried to follow this...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    NewStep= Table.FromRows(Table.Group(Table.AddIndexColumn(PreviousStepName,"idx"),{"User ID","idx","Time Off Date"},{"n",each {[User ID]{0},List.Min([Time Off Date]),List.Max([Time Off Date]),Table.RowCount(_)}},0,(x,y)=>Byte.From(y[User ID]<>x[User ID] or Duration.Days(y[Time Off Date]-x[Time Off Date])<>y[idx]-x[idx]))[n],{"User","Vacation Occurence Start Date","Vacation Occurence End Date","Vacation Duration in Days"})

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

NewStep= Table.FromRows(Table.Group(Table.AddIndexColumn(PreviousStepName,"idx"),{"User ID","idx","Time Off Date"},{"n",each {[User ID]{0},List.Min([Time Off Date]),List.Max([Time Off Date]),Table.RowCount(_)}},0,(x,y)=>Byte.From(y[User ID]<>x[User ID] or Duration.Days(y[Time Off Date]-x[Time Off Date])<>y[idx]-x[idx]))[n],{"User","Vacation Occurence Start Date","Vacation Occurence End Date","Vacation Duration in Days"})

  • JanoLehocky's avatar
    JanoLehocky
    Icon for Helper I rankHelper I
    3 years ago

    Thank you so much Daniel

     

    This worked perfectly for my requirements.

     

    I hope you have a fantastic day.

     

    Cheers

    Jano