Forum Discussion
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 May 2021?
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 )
7 Replies
Column = IF(CALCULATE(MAX('Table'[Date]))=MAX('Table'[Date]),1)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)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.
Just FYI, if it's a calculated column, then you can use the raw column name and check if
'Table'[Date] = MAX ( 'Table'[Date] )🙏 😀
