Employee Service Milestones

jamie.bryan · August 17, 2021, 9:38am

I’m working with a set of data that, amongst other things, has each employees start/end date (if they’ve left).

I’ve been tasked with adding a table to a report of employees who are reaching a service milestone in the next 30 days. At the moment, I’m looking for 1, 2, 3, 4, 5, 10 and 25 year milestones using this snippet in M Query:

#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Upcoming Milestone", each if [Leave Date] <> null then null
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 1 ), 30 ) then "1 year"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 2 ), 30 ) then "2 years"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 3 ), 30 ) then "3 years"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 4 ), 30 ) then "4 years"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 5 ), 30 ) then "5 years"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 10 ), 30 ) then "10 years"
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 25 ), 30 ) then "25 years" else null),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Days Until Milestone", each if [Leave Date] <> null then null
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 1 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 1 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 2 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 2 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 3 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 3 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 4 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 4 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 5 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 5 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 10 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 10 ) - Date.From(DateTime.LocalNow() ) )
else if Date.IsInNextNDays( Date.AddYears( [Start Date], 25 ), 30 ) then Duration.TotalDays( Date.AddYears( [Start Date], 25 ) - Date.From(DateTime.LocalNow() ) )
else null)

I’ve got 2 columns, 1 for which milestone is coming up and 1 for the days until that milestone. I’ve got 2 questions:

Is there a better way I can be doing this rather than a big if statement?
Ideally I’d like it to look for any milestones between 1 - 25, so would like a way of looping it if possible rather than 25 if statements.

Attached is the data cut down to play around with.

Employees.xlsx (17.6 KB)

Thanks!

Melissa · August 17, 2021, 12:28pm

Hi @jamie.bryan,

See if this meets your requirement.
Add a new blank query and paste this M code in, call it: fxMilestones

( StartDate as date, EndDate as nullable date ) =>
let
    curDate = Date.From( DateTime.FixedLocalNow()),
    Source = List.Generate(
            () => [MileStone = 1, TestDate = Date.AddYears( StartDate, 1 ), Today = curDate], 
            each [TestDate] <= Date.AddDays( [Today], 30 ) and [MileStone] <= 25, 
            each [MileStone = [MileStone] + 1, TestDate = Date.AddYears( [TestDate], 1 ), Today = [Today]]
        ),
    CreateTable = Table.LastN( Table.FromRecords(Source), 1 ),
    GetLastDate = CreateTable[TestDate]{0},
    Result =  
            if (EndDate = null or ( EndDate >= curDate and GetLastDate <= EndDate )) and not Table.IsEmpty(CreateTable) and Date.IsInNextNDays( GetLastDate, 30) 
            then Text.Combine( List.Transform( { CreateTable[MileStone]{0}, Duration.TotalDays( GetLastDate - curDate ) }, Text.From), "|" ) 
            else null
in
    Result

.
Next add another new blank query and paste this M code in.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVq5si2rDf2XHb9dhZjJ7cCBE5fLySv//2+40YAGdvvd6NyzFkIIIZbo8+efn5aef/D545PGN+Xvqs+PBb6QvjA+//2DCXkT2jf1b0rPj3l84eE0JRQlbK4C215CewHoBKTyTWvP+RCWMznI5B65aM7HP0uYbGFzNmFuY1CUsIelimtJdm783/b28QtovWl+oSoB2KPHuT0LVFyvIey1bM/md2xjUPbSzdyAAXl+lb9jrzQ/hMfIUkIlu88UYzjnGm/G+E7vdT/rnXv6nKPXg+Z8Jpre5F5DXtvFAOwIQd+LQ+Cxt6OpMc6JQvhMO+evOTOoU78JmDpLCQtDqYHIhWP9TLF+WqiecDu5I5Yrrvs3oXN6zO9KPwnDExTAHMt77tUdsDikNQBFUrERcC2nAAfkMek2o+CmpgPcIwv/Nr8R8LCVnVNIgIVHpiuh8W7VNwudEuLJlBfC4GTu0Xs5jett5GKPnrl/7nNNnqDAjlgGnNMD2QP7HLvcquWkL9Wta876V4TmCQp09vZ15ODNqHEkHoO2I/UyclGMd31JvB/LFpfGp/IpLt50gzcge+A6Aa2cRMfbQAHJqRyBxkWUgedUPyXLeil1bxDhWmcbp3b4u6FNLoEXIMd7ELCL3TPnPISePIE2yJyADuz1DKZ7fgMKO3MBlQ/yBezIQP4B9DdgeOAKVZ98A14jF+/bCgBdS+m+eAfQfbWB8muugYHDGx4J0DBXdGNH4X2bX3A1YOyI5I5JVB0gdacQAHhmn7vjEDpPVOLIwQB88RajkvZcS4cwP3LJ48hrOTN5ggKctTeQ3wBTTpoDZFGVgOt4TUmJRYT9Y7EBmJ2L/wO4+wTv5Z/A5IM5CMh4us3Cl6kSfBQB7GFZEAgxdCvztj3AoCm2pNHTtAqvRQiXheoJV2BW42M8iaCABOQCBm/CejM5KfOFkEMNgJQCIXgNVM9Ietq5IXEZ3cuRKm63EY4OBhatl+nK+ZyJwImQlNA8QYHugbhuSONtJAZkHuCJ6s6UooTFlayEkSSUsciSt6HGAwplSyC1rbEGSaEZTXMROfI+RgpqIPQ4d/MEBfpfmR5/RZieQAlqdgmFsyHsZMmmYwISzm6KHRj1HoUz5Nt7FMwGIO2sogZEMAOoc7Yxgqw55voeyCr/bMek687cc+1A+5HzDeBAbIHyS8wA6uEn+aQJ4x81EEU6iFD6AfXw1j6FujQsx3YTUO5uQiOCAtIQXED/nF5NWjzbdALKW24H4SNi2uwrytxtYRHhXi/L3L2DPwkoczN2aXQ4KDPUhwp66f4mZCZ0ItxTyD01iKCASJYL0LOEAL8A6C7V7gmJXgA0MHV4ggLLA7FNh5YCIS6nyYmAYBpFa+4/ABate5AH2kdkLhUgvD9NbFG0UoWHX+oIULRSMSUCdo3WAgsSWiQdT79eqaRMuGKNIpauFiTAwHRTH0jEZkwhuXBcaezSjHUiKMDFSkYeYMgpFJPTiz5AVZgXT2RHFm9yK/5mvR01EFZY72iesDcFnAXUAqh+xYLfFFKJ0ibRrrm7EVWiJWBDYy0sTurEm4J9uLGAMjLDIVyJgXIyTyVAJEgPBpoYzd6VIi8PId7ws3qCAs0DmRLCzN1pQiEA1h8jXOZ4M406J/GOkADvzqml+e7q9xKZxUCOZ22BJyiQP9I62zl1OUs6kEaERDmn1WiJIJ88N+kSY0F052ILmVuMQ+jkHPC6r5Ra3N8LoUDYzrU02WVBdv2Zuv/Fsl2eL/SCy4k1AmWiqAJ1MlOzWPj+l9cT3dZMehSVHdiOJafqAVLa1jl5hqxMWH79+TzgVju3rj/LAy4NO0dqKUF0aYvOsUo9IyFEDqTN70Sgjt8S5JGtB9N0u6UfQLF2zpxDCZxHFCY7UlTVigCnkAD0UmW2j3To4rf0Uz00hNTyAvcv1rQIvwhk1lgC8PO3LuM82GYiAC1Z58zZExSQQ1ciUNmZHoGmZ7PzIcs2AFly6BopOqxFYPoRuyJn5/6yizIj6WFW+hOSYMU6U0Av4/6RxyBzmAqrMSo11rS5c4yA1KAXeWYccSSenHo9peSi7S81IyscfVSqLA3l9hv2dJXpCQqsFwCVKU80NBZqkh5iEys/OzJ7IOHrpgkdKtKfI+sbgAdiqElSzBoAVKTc20+9Xoy3g2ObOYTUoxkL8qBUTozDFMv+9uyrEpp5S5UXuuEI8nTNBAWyB27TxRMy+anLQ41rCIAPOeb2QK2bG9ccO3d/A4YHSgrXSZtvI+UZcrwsp8tD9ggju3k79kDWTfWAeX70AF76g1+5LKCPTMAfqfxh6v3NJOZQ+QHMYDJ81spd7rA48nwvvAAWkDcg6mFFoOgFMz+nq1QnBkfkEFokSKe87DJMEg3T92lKWAvDE86PhzA9QYH1AszkgSv1JwRCD3POfDkVLPBtf89d34BmE+ecDY2UldhSTdx5nCIYs0bKpczkh8tDWKH+otQ2BKqbZgqR3MdC8W9HWSS3EOicWguZrs9jYfr3ybz0QxHYd6u8qgdI2NuR7W1k53AxcOngJZ9nr5HSa2cCcg0xX8sT+PY+ASkpBQvT53dJ4AkK8FeCGyhvgDQlJQLtDTAae31OX2e8k8+xQhh+P0uS9q1F07JHPQCgn9ACIMpxfo/8tBtcjrZecaTpWz3AKQExVMDy5wYGrxITxAKS7zkCEpdKQDy4xf/WjMyy9S0C+W1EeQNk69kUYO9inWhcv6+5+kdanQBgtWg/gMWRmATw2s8tXlAJ44u9RmRZZwp4AntrLMjT7HqbQtTL6xSVD9sK3hdRLTI3llw7snMZTXHkOLeh+2pTzpNsHEGCN3MRtQC8AfkN4EZbgBw68VL5730g5meVBcOXvgng3znZkfLXK0xQQD5/lQjIX+JcwHoBSKeuH0B+A8ob0HgbWgS02QvA4HS7ALm7awTk09kIQOcdpcbtHAMN5nk6nWbk3/71j//83Sj1680BCfn/EP75b+jyKs+vmOQeP3NuPLyTpoj3gFePk25UnLvjB//v/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp Ref" = _t, #"Start Date" = _t, #"Leave Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Leave Date", type date}}),
    ReplaceWithNull = Table.ReplaceValue(ChType,"",null,Replacer.ReplaceValue,{"Leave Date"}),
    AddMilestones = Table.AddColumn(ReplaceWithNull, "Custom", each fxMilestones([Start Date], [Leave Date])),
    SplitMilestones = Table.SplitColumn(AddMilestones, "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Upcoming Milestone", "Days Until Milestone"})
in
    SplitMilestones

.
Generated these results.

I hope this is helpful.

jamie.bryan · August 17, 2021, 12:46pm

Hi @Melissa

That works perfectly with the added bonus that I semi understand what it’s doing, thanks! I’ll pick it a part a bit more so it sinks in.

Definitely need to check out your Power Query class so can get a better grasp!