Problem Statement:
An e-commerce company delivers the orders placed by customers and need to track the get the latest status based on the datetime selection from date dimension table.
Other real time situation is to get the latest employee designation based on the latest datetime selection.
Sample Data:
An Order Tracking Transaction table is given to us as below. It has multiple orders with order status and the date stamp.
Solution Approach:
Let us understand the requirement with few examples:
If 5th Jan is selected in slicer of date dimension, then the latest statuses of available orders should be displayed as 101 – Out for delivery and 102 – Out for delivery.
If 1st Jan is selected in slicer of date dimension, then 101 – Order Placed should be the only record should display.
If 7th Jan is selected in slicer of date dimension, then 101 – Delivered and 102 – Delivered records should be displayed.
A date dimension is already available for us (if not it can be created in Power BI), if date table and Order Tracking Transaction table are made relationship based on the date fields, the result will be not desirable as the date table will filter out the fact table and only the records out of the filtered data will be displayed. But our requirement is different which should display the latest order status.
So, to get this, relationship between fact and date tables are not made and handled in measure with the help of DAX as below with comments.
Lastest Order Status =
Var _Maxdate = LASTDATE(‘Date'[Date])
// Selected Date from Slicer from Date Dimension table.
Var _OrderTrackingBeforeMaxdate =
FILTER(
‘Order Tracking’,
‘Order Tracking'[Date] <= _Maxdate
)
// Filtering the Fact table records which are available on or before selected from slicer.
Var _LatestOrderStatusDate =
MAXX(
_OrderTrackingBeforeMaxdate,
‘Order Tracking'[Date]
)
// Get the Max (latest) date from Fact table for each order id
Var _LatestOrderStatus =
LOOKUPVALUE(
‘Order Tracking'[Status],
‘Order Tracking'[Date],
_LatestOrderStatusDate
)
// Get the Latest Order Status from the Fact table by looking up the Date from Latest Date
(Max date)
Return
IF (
COUNTROWS(VALUES(‘Order Tracking'[OrderId])) = 1,
_LatestOrderStatus
)
// To display only the Latest Order Status without the Entire row.
The above expression in Return block; return value when there is one order id in the filter context, that the total row of the table won’t show anything, but the detail rows would.
Bring the Order Id from fact table and created measure (Latest Order Status) into a table visual, also create a slicer with dates from Date table as list. Play with the visuals as below
Hope you have gained some positives from this article, thanks.
Srikanth K
Data Analyst
Addend Analytics