Forum Discussion

HankScorpio2's avatar
HankScorpio2
Helper I
11 months ago
Solved

Create New Column with True False outcome based on date and id

Hi    I have a few years worth of consultant metric data. I want to create a custom column that is based on the lowest date of the first occurrence of a consultant id and mark New in the column for...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    11 months ago

    M Code:

    NewStep=let a=Table.Group(YourSourceName,"Agent Id",{"MinDate",each List.Min([Interval Start])}) in Table.AddColumn(YourSourceName,"New Starter", each if [Interval Start]<=a{[#"Agent Id"=[Agent Id]]}[Interval Start]+#duration(70,0,0,0) then "New" else "No")

     

    DAX Code:

    NewCalculatedColumn=VAR _mindate=CALCULATE(MIN(YourTable[Interval Start]),ALLEXCEPT(YourTable,YourTable[Agent Id])) RETURN IF(YourTable[Interval Start]<=_mindate+70,"New","No")

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
11 months ago

M Code:

NewStep=let a=Table.Group(YourSourceName,"Agent Id",{"MinDate",each List.Min([Interval Start])}) in Table.AddColumn(YourSourceName,"New Starter", each if [Interval Start]<=a{[#"Agent Id"=[Agent Id]]}[Interval Start]+#duration(70,0,0,0) then "New" else "No")

 

DAX Code:

NewCalculatedColumn=VAR _mindate=CALCULATE(MIN(YourTable[Interval Start]),ALLEXCEPT(YourTable,YourTable[Agent Id])) RETURN IF(YourTable[Interval Start]<=_mindate+70,"New","No")