Column with Highest Date in a year

[PaulCz]

Hi,

which formula do i have to use to calculate the highest date in a year? See Screenhot.

[PaulCz]

Thanks so much

[Akash_Varuna]

Hi @PaulCz You could achive this with custom column and grouping by year please try this

• Group by Year:

  • Go to Transform > Group By.
  • Group the data by Year.
  • In the grouped table, create a column called MaxDate that calculates the Maximum Date.

• Merge Back the Max Date:

  • Expand the grouped table to bring back the MaxDate for each year into the original table.

• Create the Highest Date Column:

  • Add a Custom Column with the formula:
    if [Date] = [MaxDate] then "True" else "False"

    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[freginier · Accepted solution]

Hi there!

if you want to mark the highest date per year in your dataset, showing "True" for the last date of the year and "False" for others, please try this formula:

HighestDateInYear =
VAR MaxDateInYear =
CALCULATE(
MAX('YourTable'[Date]),
ALLEXCEPT('YourTable', 'YourTable'[Year])
)
RETURN
IF('YourTable'[Date] = MaxDateInYear, TRUE(), FALSE())

With this formula , MAX('YourTable'[Date]) will get the highest date.
and using ALLEXCEPT('YourTable', 'YourTable'[Year]) will keep the filter only on Year, ignoring other columns.

Hope this helps!

😁😁