Create a flag for the latest date before today and another flag for the closest date last year

[Flpowerbi01]

Hi All, 

I am trying to create a flag for the latest date before today and another flag for the closest date last year. Below shows what I want to achieve, this would be the third column: 

Date	Date Description	Latest Date and Latest Date Last Year
07/06/2023	0	N
10/05/2023	0	N
12/04/2023	0	N
15/03/2023	0	N
15/02/2023	1	Y
18/01/2023	2	N
21/12/2022	3	N
23/11/2022	4	N
26/10/2022	5	N
28/09/2022	6	N
31/08/2022	7	N
03/08/2022	8	N
06/07/2022	9	N
08/06/2022	10	N
11/05/2022	11	N
12/04/2022	12	N
16/03/2022	13	N
16/02/2022	14	Y
19/01/2022	15	N
22/12/2021	16	N
24/11/2021	17	N

This would be the table if today() was 22/02/23.

Thank you so much!

[Flpowerbi01]

Thank you so much for this! 😀

[FreemanZ · Accepted solution]

hi @Flpowerbi01 

try to add a calculated column like:

FlagColumn =

VAR _date1 =

MAXX(

     FILTER(

        TableName,

        TableName<TODAY()

    ),

   TableName[Date]

)

VAR _date2 =

MAXX(

     FILTER(

        TableName,

        TableName<EDATE(TODAY(), -12)

    ),

   TableName[Date]

)

VAR _date3 =

MINX(

     FILTER(

        TableName,

        TableName>EDATE(TODAY(), -12)

    ),

   TableName[Date]

)

RETURN

IF(

    [Date] IN {_date1, _date2, _date3},

    "Y", "N"

)