Create New Column for Latest Date

[natasha519]

I have a date column (Jan 2015 - April 2021). How can I create a new column (NOT MEASURE) that will give me 1 for the latest date (e.g., April 2021) and if we get new data (May 2021), the 1 moves to May 2021?

[smpa01 · Accepted solution]

@natasha519 

Column = IF(CALCULATE(MAX('Table'[Date]))=MAX('Table'[Date]),1)

[AlexisOlson · Accepted solution]

If you check your actual data table, you won't see a 2.

I'm guessing you used LatestDateCol as an implicit measure in a report visual and it automatically aggregated two rows of the table with the same date by adding up 1 for each row.

[smpa01 · Accepted solution]

@natasha519 

Column = IF(CALCULATE(MAX('Table'[Date]))=MAX('Table'[Date]),1)

[AlexisOlson]

Just FYI, if it's a calculated column, then you can use the raw column name and check if

'Table'[Date] = MAX ( 'Table'[Date] )

[smpa01]

🙏 😀

[natasha519]

Would you happen to know why I get a value of '2'?

I did this: 

LatestDateCol = IF(CALCULATE(MAX(Provisional_Data[Date]))=MAX(Provisional_Data[Date]),1,0)

[AlexisOlson · Accepted solution]

If you check your actual data table, you won't see a 2.

I'm guessing you used LatestDateCol as an implicit measure in a report visual and it automatically aggregated two rows of the table with the same date by adding up 1 for each row.

[natasha519]

Got it! That's helpful. And if I wanted to create another column which now calculates 1 year minus the LatestDate, how would I go about that? I tried to reference the column I just created in the new column but got a circular dependency error.

[AlexisOlson · Accepted solution]

Try this as a calculated column:

LatestDatePY =
VAR MaxDate = MAX ( Provisional_Data[Date] )
VAR MaxPriorYear = DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) )
RETURN
    IF ( Provisional_Data[Date] = MaxPriorYear, 1, 0 )