topic Re: Date exctracting from specific timestamp in DAX Commands and Tips

Date exctracting from specific timestamp

Anonymous — Tue, 02 Jul 2019 10:55:08 GMT

Hi community,

Would you please give me advice what is the best way to extract the date from this specific timespamp:

P2019Y3M11DT11H4M54S

I've tried to use MID function to exctract the parts and concatenate them by ".":

Date =

MID ( Test_table[orderDate] ; SEARCH ( "M" ; Test_table[orderDate] ) + 1 ; SEARCH ( "D" ; Test_table[orderDate] ) - SEARCH ( "M" ; Test_table[orderDate] ) - 1 ) & "." &

MID ( Test_table[orderDate] ; SEARCH ( "Y" ; Test_table[orderDate] ) + 1 ; SEARCH ( "M" ; Test_table[orderDate] ) - SEARCH ( "Y" ; Test_table[orderDate] ) - 1 ) & "." &

MID ( Test_table[orderDate] ; SEARCH ( "P" ; Test_table[orderDate] ) + 1 ; 4 )

As result I received a text string "11.3.2019", but not able to change the format to Date.

Would be really appreciate for your help.

Thank you!

Re: Date exctracting from specific timestamp

AliH — Tue, 02 Jul 2019 11:17:59 GMT

Hi Egor,

test:=date(mid("P2019Y3M11DT11H4M54S",find("P","P2019Y3M11DT11H4M54S")+1,4),mid("P2019Y3M11DT11H4M54S",find("M","P2019Y3M11DT11H4M54S")+1,2),mid("P2019Y3M11DT11H4M54S",find("Y","P2019Y3M11DT11H4M54S")+1,1))

Probably not the best way, but this works.

Re: Date exctracting from specific timestamp

TomMartens — Tue, 02 Jul 2019 11:18:55 GMT

Hey,

I would do something like this:

DATE(
    VALUE(< something that returns the year >)
    ,VALUE(< something that returns the month >)
    ,VALUE(< something that returns the day >)
)

Regards,
Tom

Re: Date exctracting from specific timestamp

Anonymous — Tue, 02 Jul 2019 12:13:36 GMT

Thank you guys, now it works!