Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RicLup
Helper III
Helper III

New row Values between end date and new start date

Hi guys;

I would like you can help me, I need to identify the DateDiff in days between end date and new start date in some assignation employee, so if the difference is >1 add the register for each day with difference with status Free.

 

Example:

Original Table:

 

Original Table.PNG

 

My result must to be like this:

 

Result Table.PNG

 

Is it possible add new row for each difference day like table above?

 

Regards and thanks for your time.

 

1 ACCEPTED SOLUTION

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

20 REPLIES 20
v-lid-msft
Community Support
Community Support

Hi @RicLup ,

 

We can create a custom column then expand it to meet your requirement:

 

1. create  a custom column:

 

let 
  i = [ID],
  sd = [Start Date],
  ed = [End Date],
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(NameOfYourLastStep,each [ID] = i),"End Date")[#"End Date"],
  NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(NameOfYourLastStep,each [ID] = i and [Start Date]>ed),"Start Date")[#"Start Date"] else null,
  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"Start Date","End Date","Status"}),
  Free =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", "Start Date"}}), "End Date", each [Start Date]), "Status", each "Free")
in 
  if 
    NextDay = null 
    or NextDay - ed = OneDay 
  then 
    Assigned 
  else 
    Table.Combine({Assigned,Free})

 

12.jpg

 

2. remove orgin columns

 

13.jpg

 

3. expand the New column and do some modify

 

14.jpg

15.jpg

16.jpg

 

All the queries are here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdI10DU0RbCMDINuxuDgzPS81RSlWB7sOI7gOY11DInQAVSGxjTF0GMF0GMFUGUJcArXPjLAGI11z0mww0UViommIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, #"Start Date" = _t, #"End Date" = _t, Status = _t]),
    NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(NameOfYourLastStep, "New", each let 
  i = [ID],
  sd = [Start Date],
  ed = [End Date],
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(NameOfYourLastStep,each [ID] = i),"End Date")[#"End Date"],
  NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(NameOfYourLastStep,each [ID] = i and [Start Date]>ed),"Start Date")[#"Start Date"] else null,
  Assigned = Table.SelectColumns(Table.FromRecords({_}),{"Start Date","End Date","Status"}),
  Free =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", "Start Date"}}), "End Date", each [Start Date]), "Status", each "Free")
in 
  if 
    NextDay = null 
    or NextDay - ed = OneDay 
  then 
    Assigned 
  else 
    Table.Combine({Assigned,Free})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date", "End Date", "Status"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"Start Date", "End Date", "Status"})
in
    #"Expanded New"

 


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.

Hi @v-lid-msft ,

The query is working, but in some rows in my model have an error when I create de Custom Column in the step one:

This is my code with one example with the error:

=let 
  i = [IS],
  sd = [StartDate],
  ed = [EndDate],
  OneDay = #duration(1,0,0,0),
  MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"],
  NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed),"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 "Pool")
in 
  if 
    NextDay = null 
    or NextDay - ed = OneDay 
  then 
    Assigned 
  else 
    Table.Combine({Assigned,Pool})

 

Error1.PNGError2.PNG 

 

Thanks for your time.

Regards!

 

Hi @RicLup ,

 

If all the rows in screenshot has same value of IS, we can change the query in custom column as following:

 

7.jpg

 

let 
  i = [IS],
  sd = [StartDate],
  ed = [EndDate],
  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 "Pool")
in 
  if 
    NextDay = null 
    or NextDay - ed = OneDay 
  then 
    Assigned 
  else 
    Table.Combine({Assigned,Pool})

 

All the query are here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdA11DY1hbCNdI0sg27G4ODM9LzVFKVYHmw4jXRMY01TX2JCwBmNdJCaJGoizwYQCDSa6xgaUaYgFAA==", 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],
  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 "Pool")
in 
  if 
    NextDay = null 
    or NextDay - ed = OneDay 
  then 
    Assigned 
  else 
    Table.Combine({Assigned,Pool})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "unassignmentStatus"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"StartDate", "EndDate", "unassignmentStatus"})
in
    #"Expanded New"


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.

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!!

 

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.

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.

 

 

 

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.

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!!

 

 

 

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.

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!

 

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.

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

 

 

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.

Thanks a lot @v-lid-msft and sorry and sorry for the delay in my follow up response, I had problems with my community access account. But currently the query works perfect.

 

Regards!!

Greg_Deckler
Super User
Super User

Eh, maybe there is some Power Query way to do that? @ImkeF @edhans ?

 

In DAX it would involve GENERATESERIES but I do not see an obvious solution.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler, Hoping some ideas from @ImkeF and @edhans please!!

 

Regards!

Hi @RicLup 

 

See if this will work. It turns this:

2020-04-26 08_58_14-Untitled - Power Query Editor.png

into this: (only showing the first screenfull of data:

2020-04-26 08_58_25-Untitled - Power Query Editor.png

 

The M code is below.

 

Here is what I did:

 

  1. Created a nested table of each user ID so I could work with each user in the aggregate.
  2. Then created a list of the full date range from the first Start Date for a user to the last End Date for the same user. The list shown is just the date's numerical value, similar to what you'd see in a date in Excel if you formatted as a number.
    1. 2020-04-26 09_01_43-Untitled - Power Query Editor.png
    2. So the list above for user 1 will be from 43,831 through 43,946, which is Jan 1, 2020 through April 25, 2020.
  3. Expanded the list and converted it to a date. Then...
  4. I did a Select Rows to see if that date was in the "active" date range.
    1. 2020-04-26 09_04_55-Untitled - Power Query Editor.png
    2. So the formula in (1) assigns the Full Date Range (2) field to a variable, then in the nested table [All Rows], which is shown in (3). It either returns 1 record, or none. The none's will become my Free status later. The 1's will be active.
  5. Added the Status column by counting the rows that step 4 returned above, and assigned Free or Active.
  6. I then expanded the [Row Selection] table generated in step 4 above to return the Start/End dates back to the main table. This introduced massive duplication, but I get rid of it later.
  7. I created New Start Date and New End Date that take into account the status of the the original Start/End date. If they are null, I use the [Full Date Range] to populate. You can see this on the Free rows below.
    1. 2020-04-26 09_10_50-Untitled - Power Query Editor.png
  8. Kept the columns I needed, then removed all dupes.

Put the code below in Power Query to follow it through and apply the logic to your model. 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BCsAgDATAv3gWYjYK9i3Sg/r/PzRUrIHmIssyJKa1wCGG3ru+TExISCuWle9oCahsAsLlEaG6iRAnj2TKm2gwi6DNGOP9AMwUl2hvNgkfI9rMOX8nVZ+gnpu+VfcD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"All Rows", each _, type table [ID=number, Name=text, Start Date=date, End Date=date]}}),
    #"Added Full Date Range" = Table.AddColumn(#"Grouped Rows", "Full Date Range", each {Number.From(
    Table.Min([All Rows], "Start Date")[Start Date])..
Number.From(Table.Max([All Rows], "End Date")[End Date])
}),
    #"Expanded Full Date Range" = Table.ExpandListColumn(#"Added Full Date Range", "Full Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Full Date Range",{{"Full Date Range", type date}}),
    #"Added Row Selection" = Table.AddColumn(#"Changed Type1", "Row Selection", each
        let
            varDate = [Full Date Range]
        in
            Table.SelectRows([All Rows], each varDate >= [Start Date] and varDate <= [End Date])),
    #"Added Status" = Table.AddColumn(#"Added Row Selection", "Status", each if Table.RowCount([Row Selection]) > 0 then "Assigned" else "Free", type text),
    #"Expanded Row Selection" = Table.ExpandTableColumn(#"Added Status", "Row Selection", {"Start Date", "End Date"}, {"Start Date", "End Date"}),
    #"Added New Start Date" = Table.AddColumn(#"Expanded Row Selection", "New Start Date", each if [Start Date] is null then [Full Date Range] else [Start Date], type date),
    #"Added New End Date" = Table.AddColumn(#"Added New Start Date", "New End Date", each if [End Date] is null then [Full Date Range] else [End Date], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New End Date",{"ID", "Name", "New Start Date", "New End Date", "Status"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

 

  1.  


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @RicLup 

this is a pattern you could use (please paste the code into the advanced editor and follow the stepsL):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJVjIFsizALEMjINPQRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", Int64.Type}, {"End", Int64.Type}}),
    CreateListsOfExistingNumber = Table.AddColumn(#"Changed Type", "Custom", each {[Start]..[End]}),
    DetermineWhichNumbersAreMissing = List.Difference( {1..14}, List.Combine(CreateListsOfExistingNumber[Custom])),
    #"Converted to Table" = Table.FromList(DetermineWhichNumbersAreMissing, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    StartColumn = Table.RenameColumns(#"Converted to Table",{{"Column1", "Start"}}),
    EndColumn = Table.DuplicateColumn(StartColumn, "Start", "End"),
    AppendNewRowsToExitingRows = EndColumn & #"Changed Type",
    #"Sorted Rows" = Table.Sort(AppendNewRowsToExitingRows,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"

 

You should group you data on ID with "All Rows"-Operation and then apply this as a function to each resulting table.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

amitchandak
Super User
Super User

@RicLup , You have create a new column

last Date = maxx(filter(Table,[ID]=earlier([ID])),[End date])

And now get the dates between this and start date. That you can done using a date calendar.

 

You have to create a new table

Something like this -
table2 = SUMMARIZE(filter(CROSSJOIN(Sheet1,'Date'),'Date'[Date]>=(Sheet1[last Date]) && 'Date'[Date]<=(Sheet1[Start Date])),Sheet1[ID],Sheet1[Name],table[Status],'Date'[Date],Sheet1[Start Date],Sheet1[End Date])

 

There you need to handle the null value for last and change status when the last date is not null

 

Also refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

You can refer the current employee code to create your new table.

Thanks @amitchandak 

 

With the new column the last date only get the earliest date for all dates by ID like this:

 

last Date = maxx(filter('Table',[ID]=EARLIER([ID])),[StartDate])

 

Table 3.PNG

 

May be I was thinking that the first step is, get by row the next start date and after count he difference days between End Date and  next start date. So the expect table could looks like tthis:

Table 2.PNG

 

In that way i could to do the series by day. Sorry if i don´t explain well or my idea it´s wrong.

Final Table:

 

Final table.PNG

Regards!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.