2 latest date with condition

[amuljono]

Hi,

I have quarterly date and want to get the latest 2 date, however there is condition in which Dec data is considered as the official one and need to be included.

For example:

31-Dec-21

31-Mar-22

30-Jun-22

31-Oct-22

the desired result is: 31-Oct-2022 and 31-Dec-2021

while for the following data:

31-Dec-21

31-Mar-22

30-Jun-22

31-Oct-22

31-Dec-22

31-Mar-23

30-Jun-23

31-Oct-23

31-Dec-23

the desired result is: 31-Dec-2023 and 31-Dec-2022.

Tried the following but no luck:

Latest 2 Dates =

VAR MaxDate = MAX('Table1'[Date])

VAR LastDateInDecember = CALCULATE(MAX('Table1'[Date]), MONTH('Table1'[Date]) = 12)

VAR LatestDate =

IF(

MaxDate = LastDateInDecember,

MaxDate,

CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < LastDateInDecember)

)

VAR SecondLatestDate =

CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < LatestDate)

RETURN

{LatestDate, SecondLatestDate}

any comments are highly appreciated.

thanks

[technolog]

First, let's always get the latest December date. Then, we'll get the latest date from the entire dataset. If this latest date is the same as the December date, we'll then get the second latest date. If not, then the two dates we want are the latest date and the December date.

Here's how you can modify your DAX:

Latest 2 Dates =
VAR LastDateInDecember = CALCULATE(MAX('Table1'[Date]), MONTH('Table1'[Date]) = 12)
VAR MaxDate = MAX('Table1'[Date])
VAR SecondLatestDate =
IF(
MaxDate = LastDateInDecember,
CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate),
CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < LastDateInDecember)
)
RETURN
{LastDateInDecember, SecondLatestDate}
So, what we're doing here is first getting the latest December date. Then, we're getting the absolute latest date. If this latest date is the same as the December date, we get the second latest date. Otherwise, our two dates are the latest December date and the absolute latest date. This should give you the desired results for the examples you provided.