Forum Discussion
Solved
Create New Column for Latest Date
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 ...
Column = IF(CALCULATE(MAX('Table'[Date]))=MAX('Table'[Date]),1)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.
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 )
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.
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 )