Date format MMMMMM YYYY from text (english)

[Pedro77000]

I'm having a problem again:

I want to calculate the amount over time (the current month and the previous months). For that I have to use the formula:

Calculate (sum (.......); previousmonth (Date table [.....]); all (Fact_Table [...])).

However, the link between the fact table and the Dimension date table is not date type but type text. This is a type field: Junuary 2019, february 2019 ... etc. This fields I got it with the formula:

Date.MonthName ([date of last extraction], "en-US") & "" & number.totext (Date.Year ([date of last extraction]))

My question is: How to transform a text field containing: Junuary 2019, February 2019, March 2019 ..... in Date type with the same format: Junuary 2019, February 2019, March 2019 ??

I know that in Power Query we can do it, but I do it in French: Janvier 2019, Février 2019 .... etc. Although my Power BI is set to English.

[Pedro77000]

Thank you very much for your help.

[v-juanli-msft · Accepted solution]

Hi @Pedro77000 

First, we should know that a column like "January 2019" can't be changed to "date" type.

However, we can format the column as "January 2019" and still keep its date type via Modeling->date format

Assume you have data below,

Instead of creating text date column, create relationship below

If you want to format the date column like "January 2019", you could add a calculated column like

FORMAT([Date],"mmmm yyyy")

Then create measures in main table

current = SUM('Table'[value])

previous month = CALCULATE([current],DATEADD('date'[Date],-1,MONTH))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.