Forum Discussion
Finding the Date Range
Hi community, I need help on how to complete my task. I was a given a dataset where it displays the job start date and end date for each staff. However, the data does not consist of the dates in which they are free and are not given any projects.
I want to create two new columns in which it shows the start date and end date of when they are not given any task and are free. For example, the staff Michael does not have tasks from 24 January to 28 January and staff John does not have tasks from 21 January to 24 January. In Michael’s scenario, I believe that there are additional calculations to be made since there are 2 jobs given to him which overlaps. So we’ll have to find the correct date for which Michael are not given any task.
The below table is what I hope to achieve where I can have some calculations in my measures instead of manually entering the date in which the staffs are not given any task
Anonymous
maybe you can try this to create two columns
Column = VAR _next=minx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Start]>EARLIER('Table'[Start])),'Table'[Start]) return if(_next<='Table'[End],blank(),'Table'[End]+1) Column 2 = VAR _next=minx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Start]>EARLIER('Table'[Start])),'Table'[Start]) return if(ISBLANK('Table'[Column]),BLANK(),_next-1)pls see the attachment below
2 Replies
Anonymous
maybe you can try this to create two columns
Column = VAR _next=minx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Start]>EARLIER('Table'[Start])),'Table'[Start]) return if(_next<='Table'[End],blank(),'Table'[End]+1) Column 2 = VAR _next=minx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Start]>EARLIER('Table'[Start])),'Table'[Start]) return if(ISBLANK('Table'[Column]),BLANK(),_next-1)pls see the attachment below
