Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Second last order

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 som...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
        Anonymous
        Not applicable
        8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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