Second last order

[Anonymous]

Hi, im new with Power BI and it feels already like a very strong program for analyze and visualize. Right now I have been using very simple measures and want to go futher but im geting stuck with some things.

We say i have this data,

Columns Member id, Order id and delivery date

Two members with 5 orders each that we have delivered to them

Member ID 10
Order ID = 27 Delivery date = 2017-09-12

Order ID = 35 Delivery date = 2017-09-17

Order ID = 42 Delivery date = 2017-09-20

Order ID = 50 Delivery date = 2017-09-27

Member ID 11
Order ID = 11 Delivery date = 2017-09-03
Order ID = 16 Delivery date = 2017-09-11
Order ID = 22 Delivery date = 2017-09-15
Order ID = 30 Delivery date = 2017-09-29

I want to in a table on my report show the second last order and if it works how many days between last and second last for each Member id.

So i want to show something like this:

Member id     Second last order     Days

10                        2017-09-20            5

11                        2017-09-15           14

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

This measure shall get you the second last order

Second Last Order =
VAR LastOrder =
    MAX ( Table1[Order ID] )
RETURN
    CALCULATE (
        MAX ( Table1[Order ID] ),
        FILTER ( Table1, Table1[Order ID] < LastOrder )
    )

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

This measure shall get you the second last order

Second Last Order =
VAR LastOrder =
    MAX ( Table1[Order ID] )
RETURN
    CALCULATE (
        MAX ( Table1[Order ID] ),
        FILTER ( Table1, Table1[Order ID] < LastOrder )
    )

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

This measure shall get you the difference in days between last and second last order

Days =
VAR Secondlastorder =
    CALCULATE (
        MAX ( Table1[Order ID] ),
        FILTER ( Table1, Table1[Order ID] < MAX ( Table1[Order ID] ) )
    )
VAR LastOrder =
    MAX ( Table1[Order ID] )
RETURN
    DATEDIFF (
        CALCULATE (
            VALUES ( Table1[Delivery Date] ),
            Table1[Order ID] = Secondlastorder
        ),
        CALCULATE ( VALUES ( Table1[Delivery Date] ), Table1[Order ID] = lastorder ),
        DAY
    )

[Anonymous]

HI @Zubair_Muhammad ,

Thanks for your solutions.

I tried the Same data and Formulas my side But the result is showing wrong.

please give any suggestions to change my Measure.

--> Days Measure showing perfectly 

Days = 
  var SecondLastOrderID = 
                   CALCULATE(MAX('Table'[OrderID]), FILTER('Table','Table'[OrderID] < MAX('Table'[OrderID])))
  var LastOrderID = MAX('Table'[OrderID])
return 
  DATEDIFF(
           CALCULATE(VALUES('Table'[Delivery Date]),'Table'[OrderID] = SecondLastOrderID),
           CALCULATE(VALUES('Table'[Delivery Date]),'Table'[OrderID] = LastOrderID),DAY)

--> Delivery date showing the last date of OrderID

regards,

Naveen

[Zubair_Muhammad]

Download file from here

[Anonymous]

Im srry for late answer but your solutions helped me alot. Thank you so much.