SQL Statement to Dynamically Use Max Value in a Column

[jackofalltrades]

I have a date column in my data, but the format and data type is not a date. The format is 01220102 which is January 02, 2022. I want to right a SQL statement in the adavanced options that will pull records that are the maximum value - X. For example, only return records ColumnX >= (Max(ColumnX)-200).

Any help is appreciated!

[jackofalltrades]

I will move to the SQL forum. My apologies for posting in the wrong area. Thanks!

[Anonymous · Accepted solution]

Hi @jackofalltrades ,

Are you connecting to the sql data source on the desktop? If so, you can try to use dax.

Create a measure.

Measure=VAR _MAXVALUE=MAXX(ALL(TABLE),ColumnX)
VAR _MAX200=_MAXVALUE-200
return
IF(MAX(ColumnX)>=_MAX200&&MAX(ColumnX)<=_MAXVALUE,1,BLANK())

Or a column. 

Column=VAR _MAXVALUE=MAXX(TABLE,ColumnX)
VAR _MAX200=_MAXVALUE-200
return
IF(ColumnX>=_MAX200&&ColumnX<=_MAXVALUE,1,BLANK())

If you want to use sql formula in SQL, then you need to go to the SQL forum.

If I have misunderstood your meaning, please provide some simple sample data without privacy information and your desired output.

How to Get Your Question Answered Quickly 

 Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.