cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Re: New row Values between end date and new start date

Hi @v-lid-msft 

Thank you very much, your help is amazing and the example works perfect with my model. My user is requesting me another two petitions, basically he needs all dates register by each ID founded.

 

For Example:

Original Table

Capture 1.PNG

 

Expect Table with the hisoric registration:

Green Color; is the register with the code you shared me in the last Reply for add rows difference between End Date and new Start Date.

Yellow Color: Is new request of my user, I need have the register between Start Date and End Date in the row

Orange Color: New request for my user, I Need Identify the last End Date register and create the day register until Today.

 

Capture 2.PNG

 

If you can help me, I would appreciate you.

 

Regards!!

 

Highlighted
Community Support
Community Support

Re: New row Values between end date and new start date

Hi @RicLup ,

 

We can add a custom column as following to meet your requirement:

 

let 
  i = [IS],
  sd = [StartDate],
  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed),
  NextDay = if MaxDay <> ed and Table.RowCount(temp)>0 then Table.Min(temp,"StartDate")[#"StartDate"] else null,
  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),
  Pool = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "StartDate"}}), "EndDate", each [StartDate]), "unassignmentStatus", each "Difference End Date and New Start Date"),
  Hisotoric = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(sd,Duration.Days(ed-sd)+(if ed = MaxDay then 1 else 0),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Assigned Historic Register")
in 
  if ed = MaxDay
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed,Duration.Days(td-ed)+1,OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextDay = null or NextDay - ed = OneDay 
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})

 

All queries are here:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdE10DQ2Q2KZAtmNxcWZ6XmqKUqwODh0WCLYRUTqMzJHYlig6YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IS = _t, Name = _t, StartDate = _t, EndDate = _t, unassignmentStatus = _t]),
    Assignment = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"IS", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Assignment, "New", each let 
  i = [IS],
  sd = [StartDate],
  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed),
  NextDay = if MaxDay <> ed and Table.RowCount(temp)>0 then Table.Min(temp,"StartDate")[#"StartDate"] else null,
  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),
  Pool = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "StartDate"}}), "EndDate", each [StartDate]), "unassignmentStatus", each "Difference End Date and New Start Date"),
  Hisotoric = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(sd,Duration.Days(ed-sd)+(if ed = MaxDay then 1 else 0),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Assigned Historic Register")
in 
  if ed = MaxDay
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed,Duration.Days(td-ed)+1,OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextDay = null or NextDay - ed = OneDay 
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "unassignmentStatus"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"StartDate", "EndDate", "unassignmentStatus"})
in
    #"Expanded New"

 

Please note that "today" in queries will only update when refresh the data, it will use UTC, if you want to adjust by timezone, For example change to utc+10, we can chang the "td" to td = DateTime.Date(DateTimeZone.UtcNow()+#duration(0,10,0,0))

 

1.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: New row Values between end date and new start date

Hi @v-lid-msft 

 

Thanks for following up on my problem
I need to consider if the assignments are covered with another end date in order that it does not take into account those differences between the end date and the new start date, I don´t know if is possible modify this code that you share me a little.

 

Currently We are not considering if the difference between last end date and new start date is being covered by another assignation

For example I´m adding the row with blue color, as you can see between 4/10/2020 to 4/25/2020 is covered for differents assignations:

Capture 4.PNG

 

I would like this don´t be considered as a new register with "DIfference End Date and New Start Date" Because I´ll have dates repeats, only consider the "Assigned Historic Register" rows

Capture 5.PNG

 

This will be the final table:

 

Capture 6.PNG

Capture 7.PNG

 

Regards, and I appreciate your help.

 

 

 

Highlighted
Community Support
Community Support

Re: New row Values between end date and new start date

Hi @RicLup ,

 

We can add a custom column as following to meet your requirement:

 

let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed),
  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  

  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")

in 
  if ed = MaxDay
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed,Duration.Days(td-ed)+1,OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})

 

All queries are here:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdE10DQ2Q2KZAtmNxcWZ6XmqKUqwOdh2WSBosidFgaIFgGxFlhZE5EhvVjlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IS = _t, Name = _t, StartDate = _t, EndDate = _t, unassignmentStatus = _t]),
    Assignment = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"IS", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Assignment, "New", each let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed),
  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  

  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")



in 
  if ed = MaxDay
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed,Duration.Days(td-ed)+1,OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "unassignmentStatus"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"StartDate", "EndDate", "unassignmentStatus"})
in
    #"Expanded New"

 

2.jpg

 

It seems there are some logic chaos within my formula, please verify it with more and complex sample condition. If you have any other questions, please kindly ask here and we will try to resolve it.


By the way, PBIX file as attached. (Query 2 contain the new row)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: New row Values between end date and new start date

Hi @v-lid-msft ,

 

Thanks, I only add a condition if the last EndDate is < to Today because i have EndDates highers than Today.

in 
  if ed = MaxDay and ed < td

 

Is possible delete the duplicate row in StartDate when the status condition is  "Last End Date Register until Today", I don´t know where find in the code to change it, may be I need to add +1 day:

Currently the result looks like this:

The yellow row should not appear, must start with April 30 according to last EndDate Assignation,

Capture 8.PNG

 

Thanks for following up on my topic.

 

Regards!!

 

 

 

Highlighted
Community Support
Community Support

Re: New row Values between end date and new start date

Hi @RicLup ,

 

We can change the formula of custom column to following:

 

 

let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed),
  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  

  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")



in 
  if ed = MaxDay and ed < td
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})

 

 

4.jpg5.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: New row Values between end date and new start date

Hi @v-lid-msft 

 

Thank you very much, the query is working, currently my user ask me to consider Assignations with the StartDate and EndDate are in the same day.

 

Example in the Row 5:

Capture 10.PNG

 

This is the error that I have:

Capture 11.PNGCapture 12.PNG

 

Is It possible modify the query for this scenario to not add row registers because the assignation start and Finish the same Day? The Idea is kept the register and the rest is execute as until now. If is necesary one leyend Text for the resulting Assignation Status Column Could be "One Day Assignation"

 

Thanks for follow up and help me with my Model.

 

Regards!

 

Highlighted
Community Support
Community Support

Re: New row Values between end date and new start date

Hi @RicLup ,

 

We can change the formula of custom column to following:

 

 

let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),

  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i and [StartDate] <> [EndDate]),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed and [StartDate] <> [EndDate]),

  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  
  Assigned = if [StartDate] = [EndDate] 
		then Table.AddColumn(Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate"}),"unassignmentStatus",each "One Day Assignation")
		else Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")

in 
  if  [StartDate] = [EndDate]  then Assigned
else
  if ed = MaxDay and ed < td
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})

 

 

All the queries are here (Query 3 in file):

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdE10DQ2Q2KZAtmNxcWZ6XmqKUqwOdh2WSBosidFgaIFgGxFlhZE5EpsIO0whTkdmI3TEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IS = _t, Name = _t, StartDate = _t, EndDate = _t, unassignmentStatus = _t]),
    Assignment = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"IS", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Assignment, "New", each let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),

  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i and [StartDate] <> [EndDate]),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed and [StartDate] <> [EndDate]),

  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  
  Assigned = if [StartDate] = [EndDate] 
		then Table.AddColumn(Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate"}),"unassignmentStatus",each "One Day Assignation")
		else Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")

in 
  if  [StartDate] = [EndDate]  then Assigned
else
  if ed = MaxDay and ed < td
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "unassignmentStatus"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"StartDate", "EndDate", "unassignmentStatus"})
in
    #"Expanded New"

 

 

1.jpg


By the way, PBIX file as attached.

 

Update:

We think there may be two more condition need to discuss, could you please provide the expected result based on following sample?

 

Sample 1 (one day row is between some preiod and before today)

 

1 Name 1 2020-4-10 2020-4-15 Assigned
1 Name 1 2020-4-9 2020-4-19 Assigned
1 Name 1 2020-4-18 2020-4-25 Assigned
1 Name 1 2020-4-27 2020-4-27 Assigned
1 Name 1 2020-4-29 2020-4-30 Assigned
1 Name 1 2020-5-15 2020-5-15

Assigned

 

 

Sample 2 (one day row is after some preiod but before today)

 

1 Name 1 2020-4-10 2020-4-15 Assigned
1 Name 1 2020-4-9 2020-4-19 Assigned
1 Name 1 2020-4-18 2020-4-25 Assigned
1 Name 1 2020-4-29 2020-4-30 Assigned
1 Name 1 2020-5-5 2020-5-5 Assigned
1 Name 1 2020-5-15 2020-5-15

Assigned

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: New row Values between end date and new start date

HI @v-lid-msft 

 

You Have right, I was waiting for one model with more Data and find the scenarios that you have mentioned, and efecttively I´ll need  consider them.

 

This is the File Sample  expect with two scenarios 

 

Regards and again thanks for follow up on my topic

 

 

Highlighted
Community Support
Community Support

Re: New row Values between end date and new start date

Hi @RicLup ,

 

Please try to use the following custom column forumla, sample is in Query 4 and Query 5 of file:

 

let 
  i = [IS],  sd = [StartDate],  ed = [EndDate],
  td = DateTime.Date(DateTimeZone.UtcNow()),
  OneDay = #duration(1,0,0,0),

  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed ),

  NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"],
  
  Assigned = if [StartDate] = [EndDate] 
		then Table.AddColumn(Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate"}),"unassignmentStatus",each "One Day Assignation")
		else Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}),

  Hisotoric = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{"Column1","StartDate"})
        ,"EndDate",each [StartDate])
        ,"unassignmentStatus",each "Assigned Historic Register"),

  Pool = Table.AddColumn(Table.AddColumn(
            Table.RenameColumns(
                Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay)
                    , Splitter.SplitByNothing()
                    , null, null, ExtraValues.Error)
           ,{{"Column1", "StartDate"}})
        , "EndDate", each [StartDate])
        , "unassignmentStatus", each "Difference End Date and New Start Date")

in 
  if  sd = ed and ed < td and ed = MaxDay
  then Table.Combine({Assigned,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else if sd = ed
  then   Table.Combine({Assigned,Pool})
else
  if ed = MaxDay and ed < td
  then 
    Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")})
  else 
    if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed
    then 
        Table.Combine({Assigned,Hisotoric})
    else 
        Table.Combine({Assigned,Hisotoric,Pool})

 


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors