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

pxg08680's avatar
pxg08680
Resolver III
8 years ago

baBI123

Can you share the data...?

 

baBI123's avatar
baBI123
Helper II
8 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    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

  • pxg08680's avatar
    pxg08680
    Resolver III
    8 years ago

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

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

    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?

  • baBI123's avatar
    baBI123
    Helper II
    8 years ago

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