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

2
votes

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.

4
dateintegerpowerbidaxreporting
what have you done so far? - Anantha Raju C
Please read how to ask. - segarci

4 Answers

2
votes

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

1
votes

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.

0
votes

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.

0
votes

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