Forum Discussion

Pedro77000's avatar
Pedro77000
Frequent Visitor
6 years ago
Solved

Date format MMMMMM YYYY from text (english)

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.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.