CODEWITHSUNDEEP
dateintegerpowerbidaxreporting
Grace
Grace

Reputation: 21

How to convert integer (112 format) in to date using Dax formula?

How to convert integer (112 format) in to date using DAX formula?

The date is in integer format 20170727 and I want to convert it to the following 27/07/2017.

Upvotes: 2

Views: 15526

Answers (4)

Sina Sartip
Sina Sartip

Reputation: 33

I created a new column and set its type to date. The DAX for the new column I used is a variation of alejandro zuleta answer.

DateType = 
 IF('DW FactMeasurement PAC Daily Report'[DateKey] <> 0,
    (LEFT('DW FactMeasurement PAC Daily Report'[DateKey],4)) & "-"
    &(MID('DW FactMeasurement PAC Daily Report'[DateKey],5,2))& "-"
    &(RIGHT('DW FactMeasurement PAC Daily Report'[DateKey],2))
  )

Upvotes: 0

kesadae11
kesadae11

Reputation: 298

Both answers are correct, but I think just partially. The full method: The full porcess:

  1. Right click on your table and select New column
  2. On the top of your editor insert the following line:YourColumnName=DATEVALUE('YourTable'[YourDateFieldINTEGER])
  3. You will got the default date/time format
  4. On the top under Modeling you can change (and set as default) any date/time format. ChangeDateFormat

I suggest you to use the same date/time format in all field it is more clear, and easier if you don't have to convert always your dates.

I hope it was useful.

Upvotes: 0

Soheil Bakhshi
Soheil Bakhshi

Reputation: 485

= DATEVALUE(FORMAT(Date[DateKey], "0000/00/00"))

Upvotes: 2

alejandro zuleta
alejandro zuleta

Reputation: 14108

One way to get this in pure DAX is:

=DATE(INT(LEFT([IntegerDate],4)),INT(MID([IntegerDate],5,2)),INT(RIGHT([IntegerDate],2)))

Replace IntegerDate by the actual name of your integer date.

However it is better perform this transformations using PowerQuery or a data integration tool of your preference.

Let me know if this helps.

Upvotes: 1

Related Questions