Min Date, Max Date and Where is My Date

Very often people need to get the measure value on the max(Date), or the min(Date), or next to max(Date), or a date relative to the min/max date etc. (Date can be in Year, Quarter, Month, Week, Day or even second.) It is often a struggle to pick out that date and its associated measure value.

The first thing that may come to our mind is to write a good conditional like

Date=Max(Date) ? Or use Window_Max(Max(Date))? Then datediff()?

I don't know how those will work. But I know something that will work and it's quite simple. No need to fight with aggregate or window functions.

To select the value associated with min(Date), we just need to write this

• if first() = 0 then [measure] end

We don't even need min() function in view.

For the value associated with max(Date), the formula is

• if last() = 0 then [measure] end

For the second day after min(Date), the formula is

• first() = -1

Whether you have guessed or not, the date that is 5 days from the max(Date) is

• last() = 5

Below is an example that shows you the relationship between a date range and first()/last().

From the table, you can now figure out the formula for any date in between, as long as you are given the offset from the first or last date.

Click the above image to download the workbook.