Want a calculate column of flag for maximum date for all NDC

[jay_patel]

Hello all,

I have data of 2 columns NDC & Dates ,and  i want to create another calculated column in same table of Flag (0,1). Please have a look on data

NDC	Dates
1	01-10-2021
1	02-10-2021
2	10-10-2021
2	11-10-2021
2	15-10-2021
3	16-02-2022
3	18-02-2022
3	21-02-2022

After:-

NDC	Dates	flag
1	01-10-2021	0
1	02-10-2021	1
2	10-10-2021	0
2	11-10-2021	0
2	15-10-2021	1
3	16-02-2022	0
3	18-02-2022	0
3	21-02-2022	1

For every NDC there is maximum date and I want a flag column where there should be 1 for maximum date and 0 for other dates.

Thanks in advance.

[amitchandak · Accepted solution]

@jay_patel , New column

new flag = if( [Date] = maxx(filter(Table, [NDC] = earlier([NDC]) ), [Date]),1,0)

[PC2790 · Accepted solution]

Hey @jay_patel ,

Use this formula:

Column = var CurrentNDCValue = NDCDataTable[NDC]
var MaxDate = MAXX(FILTER(ALL(NDCDataTable),NDCDataTable[NDC]=CurrentNDCValue),NDCDataTable[Dates ])
return
IF(NDCDataTable[Dates ]=MaxDate,1,0)

Outome:

[amitchandak · Accepted solution]

@jay_patel , New column

new flag = if( [Date] = maxx(filter(Table, [NDC] = earlier([NDC]) ), [Date]),1,0)