Create a new column and have it display in number values

Question

I have 1 file which has the following columns: Document Date and Disposition Date.

In Power BI Desktop, I'd like to create another column called 'Duration' which can be calculated by taking Disposition Date - Document Date and I want the new column to display in number values since both the Disposition Date & Document Date are either in serial number (ex: 39448) or date (ex: 09/25/2018) format.

Is there a code or something to do this? Thank you!

If you took the [tour]. You'd write an MCVE. Check the [help]. — FreeMan, Sep 25 '18 at 20:00

score 0 · Accepted Answer · answered Sep 25 '18 at 19:45

I may be missing the point here, but if you have a dataset such as this:

Document    Disposition
25.09.2018  22.09.2018
24.09.2018  21.09.2018
23.09.2018  20.09.2018
22.09.2018  19.09.2018
21.09.2018  18.09.2018
20.09.2018  17.09.2018
19.09.2018  16.09.2018
18.09.2018  14.09.2018
17.09.2018  13.09.2018
16.09.2018  12.09.2018
15.09.2018  11.09.2018
14.09.2018  10.09.2018
13.09.2018  09.09.2018
12.09.2018  08.09.2018
11.09.2018  07.09.2018
10.09.2018  06.09.2018
09.09.2018  05.09.2018
08.09.2018  04.09.2018

Then you can load them using Get Data, go to Edit Queries, select Add Column, and simply set it up like this:

Then you can click the ABC / 123 icon on top of the column and change the datatype to Whole number, and you'll get this:

Please let me know if this is not what you were looking for.

Hi, thank you so much! This is what I am looking for! – T D Sep 26 '18 at 17:06 — T D, Sep 26 '18 at 17:06

score 0 · Answer 2 · answered Sep 26 '18 at 00:14

First create two new date columns for document and disposition since there are some variances in datatype. I am basically just checking if after conversion, there is a "/" in the date field implying it is a date type, if not I am assuming it is serialized and will convert. The following DAX should do it BUT it is not tested, so try it out.

True Document Date :=
SWITCH (
    TRUE (),
    AND (
        ISERROR ( SEARCH ( "/", FORMAT ( [Document], "text" ) ) ),
        [Document] >= 32767
    ), FORMAT ( DATE ( 2000, 1, [Document] - 36523 ), "YYYY-MM-DD" ),
    AND (
        ISERROR ( SEARCH ( "/", FORMAT ( [Document], "text" ) ) ),
        [Document] < 32767
    ), FORMAT ( DATE ( 1900, 1, Sheet1[DATE SERIAL NUMBER] ), "YYYY-MM-DD" ),
    NOT ( ISERROR ( SEARCH ( "/", FORMAT ( [Document], "text" ) ) ) ), [Document]
)

True Disposition Date :=
SWITCH (
    TRUE (),
    AND (
        ISERROR ( SEARCH ( "/", FORMAT ( [Disposition], "text" ) ) ),
        [Disposition] >= 32767
    ), FORMAT ( DATE ( 2000, 1, [Disposition] - 36523 ), "YYYY-MM-DD" ),
    AND (
        ISERROR ( SEARCH ( "/", FORMAT ( [Disposition], "text" ) ) ),
        [Disposition] < 32767
    ), FORMAT ( DATE ( 1900, 1, Sheet1[DATE SERIAL NUMBER] ), "YYYY-MM-DD" ),
    NOT ( ISERROR ( SEARCH ( "/", FORMAT ( [Disposition], "text" ) ) ) ), [Disposition]
)

Then, just take the difference in days and store results a new calculated column:

Date Delta :=
DATEDIFF ( [True Document Date], [True Disposition Date], DAY )

Create a new column and have it display in number values

2 Answers2