Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |