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 the first 10 weeks. And for all other dates past that occurrence for that id mark No. Something like this but for every id across all the years. From the first date the id occurs until 7 weeks later. I tried it in excel but it only does it for the first 70 days and ignores all new ids after that time. =LET(AgentStartDate, MIN(FILTER([Interval Start], [Agent Id] =[Agent Id])), IF([@[Interval Start]] <= AgentStartDate + 70, "New", "No")) Interval StartAgent IdAnsweredTotal HandleTotal TalkTotal HoldTotal ACWNew Starter13/05/20219c9f586a-ca35-4445-acfb-27f54bea5b0159421955942219111153400000New14/05/20219c9f586a-ca35-4445-acfb-27f54bea5b0178350234351966814285273403000New17/05/20219c9f586a-ca35-4445-acfb-27f54bea5b011920776445779616265482966435000New18/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012018153014899091738301065335000New19/05/20219c9f586a-ca35-4445-acfb-27f54bea5b013220314041845787348936046967000New20/05/20219c9f586a-ca35-4445-acfb-27f54bea5b0129223767361173281934339967216000New21/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012818093604783743339395166320000New24/05/20219c9f586a-ca35-4445-acfb-27f54bea5b0135230296791117950743606757495000New26/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012719802255863521433586657812000New27/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012716110715779181528813835443000New28/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012517046237795542839695235126000New31/05/20219c9f586a-ca35-4445-acfb-27f54bea5b012413956113593930923867765631000New2/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012016275640644173658061124030000New12/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012113127777521983934755074436000New13/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012115663483698796136394965040000New14/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012111925114470295532210294003000New15/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b01324822111155997538214788000New16/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b01146793727418914874845000New19/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b015351950510530488857361581000New21/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b0196581368333642519817541264000New22/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012212216844605865132879042873000New23/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012416827237886092429222795047000No26/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b012517377667868152041313024568000No27/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b011612937312518244446088063147000No28/07/2021 0:009c9f586a-ca35-4445-acfb-27f54bea5b01422772631188650472865616000No

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")

HankScorpio2

Helper I

11 months ago

Solved

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

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 the first 10 weeks. And for all other dates past that occurrence for that id mark No.

Something like this but for every id across all the years. From the first date the id occurs until 7 weeks later. I tried it in excel but it only does it for the first 70 days and ignores all new ids after that time.

=LET(AgentStartDate, MIN(FILTER([Interval Start], [Agent Id] =[Agent Id])), IF([@[Interval Start]] <= AgentStartDate + 70, "New", "No"))

Interval Start	Agent Id	Answered	Total Handle	Total Talk	Total Hold	Total ACW	New Starter
13/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	5	9421955	9422	1911115	3400000	New
14/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	7	8350234	3519668	1428527	3403000	New
17/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	19	20776445	7796162	6548296	6435000	New
18/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	20	18153014	8990917	3830106	5335000	New
19/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	32	20314041	8457873	4893604	6967000	New
20/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	29	22376736	11732819	3433996	7216000	New
21/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	28	18093604	7837433	3939516	6320000	New
24/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	35	23029679	11179507	4360675	7495000	New
26/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	27	19802255	8635214	3358665	7812000	New
27/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	27	16110715	7791815	2881383	5443000	New
28/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	25	17046237	7955428	3969523	5126000	New
31/05/2021	9c9f586a-ca35-4445-acfb-27f54bea5b01	24	13956113	5939309	2386776	5631000	New
2/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	20	16275640	6441736	5806112	4030000	New
12/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	21	13127777	5219839	3475507	4436000	New
13/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	21	15663483	6987961	3639496	5040000	New
14/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	21	11925114	4702955	3221029	4003000	New
15/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	3	2482211	1155997	538214	788000	New
16/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	1	467937	274189	148748	45000	New
19/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	5	3519505	1053048	885736	1581000	New
21/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	9	6581368	3336425	1981754	1264000	New
22/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	22	12216844	6058651	3287904	2873000	New
23/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	24	16827237	8860924	2922279	5047000	No
26/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	25	17377667	8681520	4131302	4568000	No
27/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	16	12937312	5182444	4608806	3147000	No
28/07/2021 0:00	9c9f586a-ca35-4445-acfb-27f54bea5b01	4	2277263	1188650	472865	616000	No

Need Help

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")

Forum Discussion

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

3 Replies

Related content

Create columns group

Creating a date column

Creating a custom column

unpivoting columns and creating datetime column

Appending columns and creating new column of old column headers