topic Re: Create a flag for the latest date before today and another flag for the closest date last year in DAX Commands and Tips

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

Flpowerbi01 — Wed, 22 Feb 2023 13:13:08 GMT

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!

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

FreemanZ — Wed, 22 Feb 2023 15:40:06 GMT

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"

)

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

Flpowerbi01 — Thu, 23 Feb 2023 08:39:10 GMT

Thank you so much for this! 😀