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.
