Forum Discussion

baBI123's avatar
baBI123
Helper II
8 years ago
Solved

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
    baBI123
    Helper II
    8 years ago

    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