CODEWITHSUNDEEP
datetimepowerbi
Andreas Uldall Leonhard
Andreas Uldall Leonhard

Reputation: 315

How to convert YYYYMM to datetime?

In PowerBi I have a dateslicer that uses a Datetime column from one of my tables.

The problem is that some of my other sources simply has a YYYYMM format for their dates, so I cannot make any relations to the datetime column.

Here I have them lined up next to each other. On the left is the Datetime column. On the right is the custom made YYYYMM date.

example

The actual day should just be the first of the month, and the time doesn't really matter, so 12:00:00 AM should be fine.

Is there a way to do this?

I've looked through all the datetime methods in an attempt to find a method that allows this to happen. Doesn't look like there's an easy way to do this.

Upvotes: 0

Views: 7413

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

You can treat the YearMonth as a string and write:

Date = DATE ( LEFT ( Table1[YearMonth], 4 ), RIGHT ( Table1[YearMonth], 2 ), 1 )

Upvotes: 3

OscarLar
OscarLar

Reputation: 1335

If you need a date column in your data table based on the yearMonth column then you can create it like this:

Date = 
DATE(
    ([YearMonth]-MOD([YearMonth];100))/100; 
    MOD([YearMonth];100);
     1
    )

Result:

enter image description here

Upvotes: 0

Related Questions