Retrieve rows for max date and 2nd to max date

[FarmerKenny]

I have a filter that retrieves the correct set of rows for the most recent date in a table: 

Occupation Summary = FILTER (

    FILTER (

'SitStat',

'SitStat'[MetricName] = "Occupation Summary"

),

'SitStat'[create_dt] = MAX('SitStat'[create_dt])

)

 I would like to add  an 'OR' type command to this query to retrieve the next highest date's rows using  this syntax, which does not work, CALCULATE(MAX(dateadd('SitStat'[create_dt], -1, Day)) . My objective is to get the highest and second to the highest set of rows in the same table.  Any ideas?

[VahidDM · Accepted solution]

Hi @FarmerKenny 

Try this:

Occupation Summary =
VAR _A =
    FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" )
VAR _1D =
    CALCULATE(
        MAX( 'SitStat'[create_dt] ),
        FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" )
    )
VAR _2D =
    CALCULATE(
        MAX( 'SitStat'[create_dt] ),
        FILTER(
            ALL( 'SitStat' ),
            'SitStat'[MetricName] = "Occupation Summary"
                && 'SitStat'[create_dt] < _1D
        )
    )
RETURN
    FILTER( _A, 'SitStat'[create_dt] = _1D || 'SitStat'[create_dt] = _2D )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM · Accepted solution]

Hi @FarmerKenny 

Try this:

Occupation Summary =
VAR _A =
    FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" )
VAR _1D =
    CALCULATE(
        MAX( 'SitStat'[create_dt] ),
        FILTER( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" )
    )
VAR _2D =
    CALCULATE(
        MAX( 'SitStat'[create_dt] ),
        FILTER(
            ALL( 'SitStat' ),
            'SitStat'[MetricName] = "Occupation Summary"
                && 'SitStat'[create_dt] < _1D
        )
    )
RETURN
    FILTER( _A, 'SitStat'[create_dt] = _1D || 'SitStat'[create_dt] = _2D )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[FarmerKenny]

@VahidDM , that was the closest to the actual solution. I appreciate your work here, unfortunately the the rows are all for the same day, just duplicated. I solved this probem over the weekend, but I had to do it in SQL, which I will post below. 

[FarmerKenny]

Sorry, that did not work either.  It's ignoring this filter and returning too many rows. 

FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ),

[AlexisOlson]

Sorry, I should have used a variable so I didn't forget about that filter in the RETURN part. (It's hard to write DAX blind...)

This is what I meant to write:

Occupation Summary =
VAR FilterMetric = FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" )
VAR Top2Dates =
    TOPN (
        2,
        SUMMARIZE ( FilterMetric, 'SitStat'[create_dt] ),
        'SitStat'[create_dt]
    )
RETURN
    FILTER ( FilterMetric, 'SitStat'[create_dt] IN Top2Dates )

[FarmerKenny]

Sorry, that did not add the previous days rows to the return set.  Thank you for your prompt response, though. 

[AlexisOlson]

The TOPN function returns the top N rows of a table, so if there are multiple rows with the same maximal date, you'll have to adjust what you're taking TOPN over.

So something a bit more complicated like this:

Occupation Summary =
VAR Top2Dates =
    TOPN (
        2,
        SUMMARIZE (
            FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ),
            'SitStat'[create_dt]
        ),
        'SitStat'[create_dt]
    )
RETURN
    FILTER ( 'SitStat', 'SitStat'[create_dt] IN Top2Dates )

[FarmerKenny]

Alexis, that is getting closer, but it doubled up on the rows for the filter, but the dates are not the highest or second highest. 

[AlexisOlson]

Sorry, I don't think I can do much better coding blind.

[AlexisOlson]

The TOPN function might work for you.

Try this:

Occupation Summary =
TOPN (
    2,
    FILTER ( 'SitStat', 'SitStat'[MetricName] = "Occupation Summary" ),
    'SitStat'[create_dt]
)

[FarmerKenny]

Thanks, I know that coding with out a database to access is tough.  I appreciate your attempts, and I will post the successful code here when I get this to work. 

[FarmerKenny · Accepted solution]

I worked on this over the weekend, and since DAX is new to me, I wrote it in T-SQL.  (Perhaps MS should support a SQL to DAX conversion utility...) 

Actual Solution