Conditional column for # of days employed

Hello - here are the steps that I recommend following...

• Create a custom table containing the minimum number of months to qualify for each label as well as the label you'd like to apply.
• Add a new column to your data table number of months elapsed between the two dates.  I have created a function for this.
• Normalize the months elapsed column in your data table by replacing values less than the specific min and greater than the specified max.
• Merge the data table with the custom table with the key columns being the months elapsed from the data table and min months from the custom table.
• Finally, expand to get the labels that should be applied to each row.

Custom Function to calculate the date difference in months:

// ****************************************************************************************************************/
//   PURPOSE
//   - Calculate the difference between two dates in months
// ****************************************************************************************************************/

let
    fn = ( DataTable as table, StartDateColumn as text, EndDateColumn as text, NewColumnName as text ) as table =>

    let
        
        date_diff = Table.AddColumn(
                 // starting table
                DataTable,
                // name of new column to be created
                NewColumnName, 
                each 
                    // declare inline variables
                    let 
                        // calculate the compelete years elapsed
                        DateDiffInYears = 
                            Date.Year ( Record.Field ( _, EndDateColumn ) ) - Date.Year ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the remaining months
                        MonthsRemaining = 
                            Date.Month ( Record.Field ( _, EndDateColumn ) ) - Date.Month ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the total number of months
                        DateDiffInMonths = 
                            ( DateDiffInYears * 12 ) + MonthsRemaining
                    in
                        DateDiffInMonths,
                        Int64.Type
            )
    in
        date_diff
in
    fn

Sample Data Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBDcAgDAN34Y0U2xFdBrH/Gg0FJKL+zj7LvRcaTRBLDdTCUVOvg7N3Y4sEhHhM7WeE78p5f/l9tlfT7ERkM14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startDate = _t, endDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}})
in
    #"Changed Type"

Sample Custom Date Range Dim

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7CgIxFEbhrYS0jnBz/3kuwgVITDGoYKXgo5jdGxxBi9N+3YGTc7TYxMPLTMcgC6d5ecTS5Jgqp7ANCrvb9XlZ0QlF2FZUxeT/2qH2qAPqiDqhJmNOzM78y9uf5/sXW8KOsCccCEfCCdCNMBE6IRU5FTkVORU5FTkVORWJikRFoiJRkahIVCQq0vrfB8ImlvIG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MinMonths = _t, Label = _t]),
    SetTypes = Table.TransformColumnTypes(Source,{{"MinMonths", Int64.Type}})
in
    SetTypes

Result

let
    Source = fnDateDifferenceInMonths(SampleTable, "startDate", "endDate", "DateDiffInMonths"),
    LowestMinMonthsInDim = List.Min(CustomDateRangeDim[MinMonths]),
    HighestMinMonthsInDim = List.Max(CustomDateRangeDim[MinMonths]),
    NormalizeMonths = Table.ReplaceValue(
        Source, 
        each [DateDiffInMonths] , 
        each 
            // if the months elapsed is less than the lowest min in the dim table
            if [DateDiffInMonths] < LowestMinMonthsInDim 
            // replace with the lowest min
            then LowestMinMonthsInDim
            else 
                // if the months elapsed is greater than the highest min in the dim table
                if [DateDiffInMonths] > HighestMinMonthsInDim then 
                // replace with the highest min
                HighestMinMonthsInDim 
                // otherwise perform no replacement
                else [DateDiffInMonths],
                Replacer.ReplaceValue,
                {"DateDiffInMonths"}
    ),
    // merge with the custom date dim
    merge = Table.NestedJoin(NormalizeMonths, {"DateDiffInMonths"}, CustomDateRangeDim, {"MinMonths"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(merge, "Table", {"Label"})
in
    #"Expanded Table"

---

jcastr02 wrote:

HotChilli   see below.

---

Then