Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 ) )
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 )
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 ) )
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 )
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
Im srry for late answer but your solutions helped me alot. Thank you so much.
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |