Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
My result must to be like this:
Is it possible add new row for each difference day like table above?
Regards and thanks for your time.
Solved! Go to 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,
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |