new calculated column using dates

[baBI123]

Hello,

I was wondering if anyone could help me with a DAX formula for a new calculated column..
I need it to do the following: 
([Last Ship Date]- [Entry Date]) - ([Quote Approved] - [Quote Date])

Thank you! Any help is greatly appreciated!



screen shot of data

[pxg08680]

@baBI123

I have taken top 3 rows from your data for first two columns and looking through other dates i have taken dates randomly for rest two columns.

And then I did these steps,

And then another column

Final result

The 3rd row gives some weird result because I entered 207 instead of 2017 for EntryDate(my bad). But rest all is working fine.

Hope this helps.

[baBI123]

HI @pxg08680Step 1 worked like a charm, thank you for that!
...However, I am having an issue with step 2, could you look at the picture and give me some insight?
Thank you

error message

[pxg08680]

@baBI123

Swap the column names and try the same.

[baBI123]

@pxg08680 

I did, andthe same message pops up 😞 

[pxg08680]

@baBI123

Do this 

[baBI123]

column 1=TAT
column 2= ACA
column 3= TAT NET

What do you think went wrong @pxg08680 ?

[pxg08680]

@baBI123

Can you share the data...?

[baBI123]

unfortunately, I can not as there is private info on the document... do yu have any other suggestions @pxg08680 ?

[pxg08680]

@baBI123

column3 has no data for few rows. So when you subtract it from column4 what do you expect the result to be...?

Date - empty row = ...?????

[baBI123]

@pxg08680 ....hmm... I believe that I am going to have to go back and clean up my data. I talked to my supervisor and there is an issue with the blanks. When I clean up the data, I will come back to you with the new data. Thank you for your help and patience. I am very new to all of this and so your patience is greatly appreciated 🙂 As soon as I clean up the data, I will comment on here again. 

[baBI123]

HELLO @pxg08680 ..... so.... I think what we are going to do is use the same data (and keep the formulas you gave me because they work GREAT!) but we need a way to calculate [NET TAT]...
If there is no value in [Last Ship Date] and [Qute date] and [Quote approved], then I neet [NET TAT] to be 0.
Do you have anyway to figure that out?

[v-yulgu-msft · Accepted solution]

Hi @baBI123,

Please create calculated columns referring to below formulas:

diff1 =
IF (
    Table1[Shipdate] > Table1[Entrydate],
    DATEDIFF ( Table1[Entrydate], Table1[Shipdate], DAY ),
    DATEDIFF ( Table1[Shipdate], Table1[Entrydate], DAY )
)

diff2 =
IF (
    Table1[Approveddate] > Table1[Quoteddate],
    DATEDIFF ( Table1[Quoteddate], Table1[Approveddate], DAY ),
    DATEDIFF ( Table1[Approveddate], Table1[Quoteddate], DAY )
)

NET TAT =
IF (
    Table1[Shipdate] = BLANK ()
        || Table1[Quoteddate] = BLANK ()
        || Table1[Approveddate] = BLANK (),
    0,
    Table1[diff1] - Table1[diff2]
)

Best regards,

Yuliana Gu

[baBI123]

THANK YOU @v-yulgu-msft !! And thank you for all you help @pxg08680! I appreciate it.

[Phil_Seamark]

Hi @baBI123

So do you have an example of what your expected outcome should be say, for the top row in the screenshot you posted?

Just to be clear what you are trying to achieve?

You have

Entry Date = 9th Aug, 2017
Last Ship Date = 11th Aug, 2017

Quote Date = blank
Quote Approved = blank

what value should appear in your new calculated column for this example?

[baBI123]

Hi @Phil_Seamark

In laymans terms, I am trying to calculate total TAT (turn around time) for products a company ships out. 
I am glad you brought up the blanks, this is also an issue that I don't know how to deal with... 
The data was taken from Excel (data originally came from an Oracle server) and there are many cells in the [Quote Date] and [Quote Approved] fields where a cell is blank for whatever reason... It may be something I need to address with my supervisor because it might affect the desired result... I dont know if I can work around this issue in POWER BI or whether we have to clean up the original data.. thoughts?

At the end of the day, what I need is a column that reads me a number. That number should tell me how many days it takes this company to turn around each product. Each row is a different product so in the long run, I am hoping to create visuals in a report that will display each products TAT based on customers, internal depts, and product number (this is the BIGGER picture)...

Thank you again, 
baBI123