Count numbers of orders in processing, transit, delivered or cancelled using DAX

[abdulhaseebm23]

Hello, 

I am abdul haseeb, I am new to dax and need to make a dynamic calculation  in shipment tracking fact table. I want to use a date slicer which should affect 4 row card and within the selected date range how many orders are currently in processing, transit, delivered or cancelled. I thought of the logic for using max date to find latest state of order but its only considering the latest state for entire table not dynamically recalculating the latest state for each order. The issue is I have a accumulated fact table where one order has multiple records for each state. Also, since processing is not final state of orders it won't even give true option for a calculated column I made.

I need help with this I have tried a few approaches but nothing works.

I would appreciate if you can atleast point me in the right direction

Shipment Tracking files link

[lbendlin]

 I want to use a date slicer 

That may not be the best approach as each order can have multiple stati in that timeframe.  What you likely need is a date selector, so you an calculate how many orders were in each status on that particular day. If you really want a slicer (time range) then you would need to plot it by end of day or end of month etc.

Additionally, you will need to use a disconnected calendar table for that date selector, as you will otherwise impact the filter context of your fact table.

Next issue is that your Date column in the fact table is actually Datetime.  That means it will not even correlate to the Calendar table.

I would propose a different graphical representation, showing the running total delivery status by day, month and year.  

You may also want to consider that the data is immutable, so another option is to prepare a calculated table that precalculates the delivery status for each order for each date.

[rajendraongole1]

Hi @abdulhaseebm23 - Derive a new column status to fact as below:

create below measure for lateststate 

LatestState =

VAR LatestDatePerOrder =

    CALCULATE(

        MAX('FactShipmentTracking'[Date]),

        ALLEXCEPT('FactShipmentTracking', 'FactShipmentTracking'[Order Id])

    )

RETURN

    CALCULATE(

        MAX('FactShipmentTracking'[Tracking State ID]),

        'FactShipmentTracking'[Date] = LatestDatePerOrder

    )

Create a measure to count orders that are in the "Processing" state within the selected date range.

OrdersInProcessing =

CALCULATE(

    COUNTROWS(

        FILTER(

            'FactShipmentTracking',

            FactShipmentTracking[status]= "Processing"

        )

    ),

    ALLSELECTED('FactShipmentTracking')

)

 Create a measure to count orders that are in the "Delivered" 

OrdersDelivered =

CALCULATE(

    COUNTROWS(

        FILTER(

            'FactShipmentTracking',

            FactShipmentTracking[status] = "Delivered"

        )

    ),

    ALLSELECTED('FactShipmentTracking')

)

Create a measure to count orders that are in the "Transit"

OrdersInTransit =

CALCULATE(

COUNTROWS(

FILTER(

'FactShipmentTracking',

FactShipmentTracking[status] = "In Transit"

)

),

ALLSELECTED('FactShipmentTracking')

)

cancelled measure as below:

Hope it works .

Create a measure to count orders that are in the "Delivered" state within the selected date range.

[abdulhaseebm23]

Hi Rajendraongole, thanks for the reply. I tried the solution you recommended it's not working. Let me explain what I mean.

Try filtering just to order id O1 you can observe on jan 1 2015 the order is in processing, on jan 2 its in transit and on jan 3 its delivered. If i use a date slicer to select start and end date as jan 1 2015 it should show processing as 1 and other states as blank or 0. On jan 2 in transit should show 1 and jan 3 delivered should be 1. I know what I am trying is difficult, but on specific days it should show how many orders are in each  state.

As you can see on Jan 3 the latest state of O1 is delivered, but its counting previous states as well and in my current approach it shows 1 only for the latest state which is delivered.

I have been working on this for over a day now can't figure out how the date slicer can effectively work on these measures.