Add column to validate if Date field is most recent date of that specific month

[Anonymous]

Hello all,

I have a table with projects on one column and Date Updated on another column as in the example below. I would like to add a new column "Most recent date" that will take value 1, if the day when the update took place is the highest in the month and 0 otherwise.

In the example below, 15 is the highest day in September and 23 is the highest day in October. That's way the value in the "Most recent date" is 1.

Any idea how to achieve this? Also, some intuition along the code will be very helpful. Thank you!

Project ID	Date Updated	Most recent date
11	02/09/2020	0
12	02/09/2020	0
13	02/09/2020	0
14	02/09/2020	0
12	08/09/2020	0
14	08/09/2020	0
12	15/09/2020	1
14	15/09/2020	1
15	15/09/2020	1
16	15/09/2020	1
11	02/10/2020	0
12	02/10/2020	0
13	02/10/2020	0
14	02/10/2020	0
12	11/10/2020	0
14	11/10/2020	0
12	23/10/2020	1
14	23/10/2020	1
15	23/10/2020	1
16	23/10/2020	1

[amitchandak · Accepted solution]

@Anonymous , a new column like

if( [Date Updated] = maxx(filter(table, format([Date Updated],"YYYYMM") = format(earlier([Date Updated]),"YYYYMM")),[Date Updated]) , 1,0)

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Result Column = 
var _maxdate = 
CALCULATE(
    MAX('Table'[DateUpdated]),
    FILTER(
        'Table',
        YEAR([DateUpdated])=YEAR(EARLIER('Table'[DateUpdated]))&&
        MONTH([DateUpdated])=MONTH(EARLIER('Table'[DateUpdated]))
    )
)
return
IF(
    [DateUpdated]=_maxdate,
    1,0
)

Measure:

Result Measure = 
var _maxdate = 
CALCULATE(
    MAX('Table'[DateUpdated]),
    FILTER(
        ALL('Table'),
        YEAR([DateUpdated])=YEAR(SELECTEDVALUE('Table'[DateUpdated]))&&
        MONTH([DateUpdated])=MONTH(SELECTEDVALUE('Table'[DateUpdated]))
    )
)
return
IF(
    SELECTEDVALUE('Table'[DateUpdated])=_maxdate,
    1,0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , a new column like

if( [Date Updated] = maxx(filter(table, format([Date Updated],"YYYYMM") = format(earlier([Date Updated]),"YYYYMM")),[Date Updated]) , 1,0)