Calculate Values from Previous Row
Harsh is a Sales Manager at XYZ
Corporation. While looking at the Sales Report, he wanted to check the numbers
of days between 2 orders from a customer. He also wants to know the previous
order (Order Date, Order No, Sales Amount) details in the same row.
While this may seem a very easy
solution in Power BI, it is also one of the most frequently asked questions
in the official Power BI community.
So, let’s try and solve this using 2
approaches.
Order Date
(Date/Time), Customer Id (Text), Sales Order No (Text), Sales Amount (Numeric).
As soon as an order is
placed, a Sales Order No gets generated.
CALCULATED COLUMNS
The idea here is to get the previous Sales Order Date, Sales Order
Number and Sales Amount aligned to next purchase of the customer.
You can use the below formula to do so
Previous Order Date =
CALCULATE (
MAX ( 'Table'[Order Date] ),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Customer ID]
),
'Table'[Order Date]
< EARLIER ( 'Table'[Order Date] )
)
)
Similarly, since the Sales Order No is generated
sequentially, you can use the similar formula.
CALCULATE (
MAX ( 'Table'[Sales Order Number] ),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Customer ID]
),
'Table'[Order Date]
< EARLIER ( 'Table'[Order Date] )
)
)
Since Dates and Sales Order No are
sequential hence it was easy to shift the dates. The Max (Sales Order No) of
the filtered table always gave you the previous value of the Sales Order No.
For Example,
Suppose the row marked in Black is the current row which is under evaluation, the table above (marked in green) gets filtered due to the value in the filter context ('Table'[Order Date] < EARLIER (Table [Order Date]) and the value returned is MAX(Order Date) which is 29-Jan-20 1:30:00 PM in this case.
But the Sales
Amount is not sequential value. If we were to follow the same step as above,
the Previous Sales Amount will return $1,192.04 while the correct value which
should be returned is $200.
To overcome
this, one way is to create an Index Column. We can Power Query and create one.
But, here is a method incase you want to do this through a Calculated Column.
You can use the RankX function to create an index Column
Rank Date =
VAR _rankSalesOrder =
RANKX (
FILTER (
ALL (
'Table'[Order Date],
'Table'[Customer ID],
'Table'[Sales Order Number]
),
'Table'[Customer ID]
= EARLIER ( 'Table'[Customer ID] )
),
'Table'[Order Date],
,
ASC
)
RETURN
_rankSalesOrder
The formula ranks the customer id
based on Order Date.
With the help of this column we can now, find the previous Sales Amount.
CALCULATE (
MAX('Table'[Sales Amount]),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Customer ID]
),
'Table'[Order Date]
< EARLIER('Table'[Order Date]) && 'Table'[Rank Date] = EARLIER('Table'[Rank Date]) - 1
)
)
Now, we can calculate the difference in Days between 2 orders.
Difference in Days = DATEDIFF('Table'[Previous Order Date],'Table'[Order Date],DAY)
PS: This is just one way to
solve this kind of problems.
Cheers,
Harsh Nathani
No comments:
Post a Comment