Measure to select 2nd row in sorted table?

[mbailey]

Hi,

I have a table that I want to sort by a date field, DESC, and then always use the 2nd row in that table in a Measure calculation. The purpose of this measure will be to return the percent_on_time_shipment for the "previous day". It occurred to me that I don' thave to try and accomplish this based on the date and trying all kinds of complex formulas with Date Tables. The result will always be the second row of data regardless of the date. So, How can I accomplish this?

Example Table:

[v-easonf-msft]

Hi , @mbailey 

You can take a try measure as below:

measure= 
var currDate =MAX('Table'[due_date])
var previous_date=CALCULATE(MAX('Table'[due_date]),FILTER(ALLSELECTED('Table'),'Table'[due_date]<currDate))
return CALCULATE(SUM('Table'[percent_on_time_shipment]),FILTER(ALLSELECTED('Table'),'Table'[due_date]=previous_date))+0

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mbailey]

Hi,

Thank you, but this doesn't seem to work in when doing a Direct Query which is my data source. I get this error: Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

I've been trying to get something like this below working, but am getting errors and can't figure out where to put something to return just the 2nd row. I assume I need to add a FILTER and MIN(DMTA_OnTimeShipmentSummary[due_date]), but not sure if in the VAR section or in the Return section.  The VAR section should give me just the top 2 rows - which would be the last two logged days, then I just need to narrow it down to the second row;

Daily Carryover =

VAR Last1Day =

SELECTCOLUMNS(

TOPN(2,

DMTA_OnTimeShipmentSummary, DMTA_OnTimeShipmentSummary[due_date],DESC

),

"PreviousDueDate", DMTA_OnTimeShipmentSummary[due_date],

"PreviousToShip", DMTA_OnTimeShipmentSummary[number_of_order_lines_to_ship],

"PreviousShipped", DMTA_OnTimeShipmentSummary[number_of_order_lines_shipped]

)

RETURN

CALCULATE(

SUM(PreviousToShip - PrviouseShipped )

) IN Last1Day

-Mike

[v-easonf-msft]

Hi, @mbailey 

I create two calculated coloumns ,the result show as below.

Which one is your expected result?

Column = 
MINX( 
    TOPN (
        2,
        DMTA_OnTimeShipmentSummary,
        DMTA_OnTimeShipmentSummary[due_date], DESC
    ),
    DMTA_OnTimeShipmentSummary[percent_on_time_shipment]
)

Calculated Column = 
MAXX (
    TOPN (
        1,
        FILTER (
            DMTA_OnTimeShipmentSummary,
                DMTA_OnTimeShipmentSummary[due_date] < EARLIER ( DMTA_OnTimeShipmentSummary[due_date])
        ),
        DMTA_OnTimeShipmentSummary[due_date], DESC
    ),
    DMTA_OnTimeShipmentSummary[percent_on_time_shipment]
)

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mbailey]

Hi,

Thank for your help, but neither of these work. The first doesn't return anything = 0, and the second doesn't work because of the EARLIER function for some reason. I ran into this before trying that. I think it has to do with a direct Query or something. Also, if you look at your results, you are adding a new column and the result is 100 each row. I'm just trying to create one Measure that will grab the Percent On-Time value for the second row in the table - which will always be the "Previous Day" regardless of dates since a record will not even exist for a "previous day" if nothing happened on a particular day.

Mindful that my data looks like this below, so in this example, I'm trying to create a measure to pick up "100.00" from row 2 that I can put on a Card visualization for "Previous Day Average".  And again, I don't care about the due_date, I just always need to get the 2nd row.

[v-easonf-msft]

Hi, @mbailey 

I’m sorry I didn’t notice that your connection mode is directquery, and  calculation column formula in my previous sample can not be applied  as measure.

However, when I re-test my first formula  as measure in DirectQuery mode, it  can show nicely the second row of data .

Please test whether the following formula will  return the date of  the second row，

If possible, please share the screenshot of the result on the desktop for further research.

measure due_date = 
MINX( 
    TOPN (
        2,
        DMTA_OnTimeShipmentSummary,
        DMTA_OnTimeShipmentSummary[due_date], DESC
    ),
    DMTA_OnTimeShipmentSummary[due_date]
)

BTY,you may  consider creating a index column in database,It will better help you solve this  problem.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @mbailey ,

as the Power BI dataset does not know a sorted structure like an index, as we know this from relational databases. For this, you have to be aware that this can be quite slow. Basically, this is what you are looking for: You are looking for the record with the maximum date that is smaller than the current date.

Please provide a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive to dropbox, and share the link. If you are using an Excel file to create sample data, share the xlsx as well.

Regards,

Tom