no valid "dates" in data model

[podma]

Dear all,

I face the issue that there's no valid date in my data model.

Data source is Python and I'm not allowed to make any changes there.

The column that contains data information has the following structure: YYYYMM (e.g. 202412). However, this is not a valid date PowerBI can work with (e.g. I would like to work with the moving average function). Is there any possibility to convert the given text format in a valid date format? In Excel I could help myself with Right / Left functions (e.g. ="01"&"."&RIGHT(D4;2)&"."&LEFT(D4;4) to get 01.12.2024). But I have no idea what to do in PowerBI.

Many thanks for your help. Kind regards, Mathias

[podma]

Many thanks

[bhanu_gautam · Accepted solution]

@podma , Create a new calculated column using the below DAX

ValidDate =
VAR YearPart = LEFT('YourTable'[DateText], 4)
VAR MonthPart = RIGHT('YourTable'[DateText], 2)
RETURN
DATE(YearPart, MonthPart, 1)

[podma]

your formula went perfectly well. As you can see in the following:

However, I got the following error message once I tried to realize a quick measure (a rolling average) based on the new column. Do you know why it's still not running?