Incorrect Results using DAX

[vtilokani]

Hello folks,

I have a table in which there are three columns. First column is Order Status which has either of the two values Delivered or Not Delivered. The second column, Delivery date, which has data in datestamp format but the type of data is "TEXT". Finally, third column is Actual date delivered which has data in datestamp format and the data type is "TEXT" .

I'm writing a DAX query to see if the order was delivered, then check if the Actual date delivered is before the delivery date, if true then "on time" otherwise "delayed" 

The query that I've written is giving correct results in some cases and incorrect in some. I don't know where I'm going wrong. Please help. 

Delivery Status = IF(AND('Nexusgoods_orders_dataset (1)'[order_status] = "delivered", 'Nexusgoods_orders_dataset (1)'[order_delivered_customer_date]>'Nexusgoods_orders_dataset (1)'[order_estimated_delivery_date]),"Delayed", "Ontime")

[HotChilli]

Good post. You provided an explanation and photo to show the problem.

--

I 'suspect' that the dates that are failing are being converted to the incorrect date format (UK or US settings) because the example shows a 30th July 2018 failing but all the ones that look ok in the picture have months and days of values less than 12 (so they would be legitimate dates in UK or US settings (though it's important to get the correct transformation because 07/08/18 and 08/07/18 are legitimate dates but not the same date.

--

You can convert text to date with (right-click column header Change Type->using Locale).  You do this before any error steps. You can't do it after an error occurs.

[HotChilli]

"but it dosent seem to work" - explain this please.  I can't help if you don't provide detail.

--

If the data is not good quality you are building on shaky ground so it doesn't matter if there are 5 rows or 100,000, it needs to be fixed.

[vtilokani]

I'm sorry for being vague and not to the point. 

When I use the option of change data type from Text to date/time in the power query editor, some of the dates are being converted but many are appearing as "Error". when  I click on the error mesage I get the

following message/

following

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = 1*(Data[order_delivered_customer_date]<=Data[order_estimated_delivery_date])

Hope this helps.

[vtilokani]

Hey Ashish,

How did you convert datatype to date and time format. I've tried it at my end but unable to do so and hence I'm getting errors. Please guide.

[Ashish_Mathur]

I did not do anything.  When i pasted the data from Excel to PowerBI, the data type was picked up correctly.

[HotChilli]

Please refer to my previous response. Get these fields changed to a date or datetime type in Power Query.  Very straightforward.  If there is an error, there may be a problem with data quality and you can fix it there.

[vtilokani]

@HotChilli I tried using your solution but it dosent seem to work. There is a lot of inconsistency in the raw data for the columns we are trying to solve the problem for. The raw data is close to 100,000 rows.

[Anonymous]

@HotChilli Thanks for your contribution on this thread.

Hi @vtilokani ,

As @HotChilli refered, the problem may be caused by the data type of field [order_delivered_customer_date] and [order_estimated_delivery_date]. Please update the formula of measure as below and check if it can return the expected result:

Delivery Status =
IF (
    AND (
        'Nexusgoods_orders_dataset (1)'[order_status] = "delivered",
        DATEVALUE ( 'Nexusgoods_orders_dataset (1)'[order_delivered_customer_date] )
            > DATEVALUE ( 'Nexusgoods_orders_dataset (1)'[order_estimated_delivery_date] )
    ),
    "Delayed",
    "Ontime"
)

If the above one can't help you get the expected result, could you please provide some sample data in these two fields with Text format? Thank you.                                                               

Best Regards

[vtilokani]

hello folks, 

I tried using the query that you had suggested but it doesn't work. I'm getting the following error "Cannot convert value '' of type Text to type Date." 

I've attached a spreadsheet link along with some sample dataset for your reference. Please help. https://docs.google.com/spreadsheets/d/19h8jUscm4Zy10o2E_SsLCDbwnfuBU_tRw2jZtuJM9vY/edit?usp=sharing 

[vtilokani]

I'm sorry. I forgot to mention that I've copied this data from the csv file that I had used to import data in to power BI.

[HotChilli]

It'll be caused by using '>' (is greater than) and the 'text' datatype of the 2 fields concerned. If they are dates/datetimes, you should probably use the appropriate datatype.