Getting Max Date before Today()

[calvintang]

Data frame:

Issue Date | Issue ID | Issue Type

2022-02-26 | 1 | Organizational 

2022-02-27 | 2 | Technical

2022-05-20 | 3 | Systems

2022-09-20 | 4 | Switchover

Ideally, I'd like to get the latest date before today(). In the scenario above, that would be 2022-05-20, instead of 2022-09-20.

May I know what is the syntax to achieve this?

Thanks

[sidhiq91]

@calvintang  Please see the script below: 

NoConcatenate
Temp:
load Date(Date#([Issue Date],'YYYY-MM-DD'),'MM/DD/YYYY') as [Issue Date],
[Issue ID],
[Issue Type]

Inline [
Issue Date | Issue ID | Issue Type

2022-02-26 | 1 | Organizational

2022-02-27 | 2 | Technical

2022-05-20 | 3 | Systems

2022-09-20 | 4 | Switchover] (delimiter is '|');

Inner Join (Temp)
Temp1:
Load Date(Max([Issue Date]),'MM/DD/YYYY') as [Issue Date];

Load
[Issue Date],
[Issue ID],
[Issue Type]
Resident Temp
where [Issue Date]<Today();

//Drop table Temp;

Exit Script;

If this resolves the issue, please like and accept it as a solution.

[calvintang]

I am sorry, but I am unable to reload the data in that fashion - is there a different way using measure? Set analysis perhaps?

[sidhiq91 · Accepted solution]

@calvintang  Please use the below expression to get the desired output.

Max({<[Issue Date]={"<=$(=Date(today(),'MM/DD/YYYY'))"}>}[Issue Date])

[calvintang]

Thank you! This worked!