Forum Discussion
Create New Column for Latest Date
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 )
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.
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 )