cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baBI123
Helper II
Helper II

new calculated column using dates

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 datascreen shot of data

1 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]
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
pxg08680
Resolver III
Resolver III

@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.

 

a1.PNG

And then I did these steps,

 

a1.PNG

And then another column

a1.PNG

Final result

a1.PNG

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.

HI @pxg08680Step 1 worked like a charmSmiley LOL, 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 messageerror message

@baBI123

Swap the column names and try the same.

@pxg08680 

I did, andthe same message pops up 😞 

@baBI123

Do this a1.PNG

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

What do you think went wrong @pxg08680 ?
ERROR 2.png

@baBI123

Can you share the data...?

 

unfortunately, I can not as there is private info on the document... do yu have any other suggestions @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 = ...?????

@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. 

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?tat.png

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]
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Phil_Seamark
Microsoft
Microsoft

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?

 

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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


Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors