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

Thank you! Any help is greatly appreciated!



screen shot of data

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

15 Replies

  • pxg08680's avatar
    pxg08680
    Resolver III
    8 years ago

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

      HI pxg08680Step 1 worked like a charm:smileylol:, 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

  • 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