How to convert YYYYMM to datetime or number?

user_12854411 0
Viewed 948

I have data in the powerbi loaded from azure analytics service. As I connect it in live mode, so I cannot open the power query in the report.

Now I need to convert YYYYMM (called calendar from the table, for instance, 201905) to number or datetime. As I do not know the default format, so I try to use date or value to get the result. However, I am not able to find the table in the formula. For example:

Value(table[calendar])

or

Date = DATE ( LEFT ( table[calendar], 4 ), RIGHT ( table[calendar], 2 ), 1 )

My question is how to convert YYYYMM to the NUMBER or DATE. Thank you.

dax powerbi
1 Answers

As you are creating a measure, you first need to make sure that it properly retrieves a date from the current table row.

mDate = 
    var ThisDate = MAX(tbl_YYYYMM[Dates])
return
    ThisDate

enter image description here

Now that we know it works properly, you can amend your measure as follows:

mDate = 
    var ThisDate = MAX(tbl_YYYYMM[Dates])
return
    DATE(
        LEFT(ThisDate, 4),
        RIGHT(ThisDate, 2),
        1
    )

enter image description here

You might now want to change the measure's datatype so that it doesn't display time:

enter image description here

P.S. I am wondering, is measure really your only option? As suggested in the comments, it might be easier to perform this transformation in DAX Calculated Column, or even at Power Query level.

user_106719260
Related