Finding the Date Range

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu · Accepted solution]

@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