Forum Discussion

Marco_RC's avatar
Marco_RC
Copper Contributor
Nov 02, 2023

Language of month's name extracted from a date in a dynamic table from Power Pivot Data Model

Hi,   I have a problem with an Excel file that has been created in Windows 10 Pro with en-US regional format. The problem is that when I open it on a computer with Windows 10 Pro with es-MX regiona...
BI & Data Analysis
excel
Formulas and Functions
office 365
power bi
Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 15, 2023

Hi Marco_RC 

 

I think I can replicate what you explained, though I'm not sure where the issue comes from... I had similar challenges in the paste and instead of wastihg time investigating the reason I built my own Calendar/Date table with Power Query (I can provide the query)

 

Fixing your particular workbook (get months in Spanish) should be doable. However, depending on how the data loads to the Data Model the approach might be different. If you could share your workbook that would be ideal...

 

An improssible quick-fix would have been:

but the 3rd argument (locale_name) of the DAX https://learn.microsoft.com/en-us/dax/format-function-dax function is currently supported in PowerBI only 

Marco_RC's avatar
Marco_RC
Copper Contributor
Nov 17, 2023

Hi Lorenzo ,

 

Thank you very much for the answer.

 

We used a similar approach through Power Query to resolve the problem. However, I'm sharing with you the sample workbook and I would like to know the details of your proposed solution, please.

Also we saw the format parameter on the MONTH dax function, and it is quite absurd that it is not supported in Excel, it would help a lot.

 

My concern is that, on a Pivot Table, when the user add a date type field, Excel automatically generate the conversion of the month and it will show the wrong value, so it is really misleading.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 05, 2023

    Hi Marco_RC 

     

    I thought you would provide (any kind of) feedback on what you asked to be shared

    Maybe you're too busy these days...

    • Marco_RC's avatar
      Marco_RC
      Copper Contributor
      Dec 06, 2023

      Hi Lorenzo ,

       

      I'm so sorry I couldn't follow up yet, but it's on my to-do list. I'll let you know as soon as possible.

       

      Thanks,

      Marco

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 07, 2023

        Hi Marco_RC 

         

        No worries at all & no rush 🙂

        In the meantime I checked with a friend of mine who has everything (OS, Regional Settings and Excel) in FR. When building a new Pivot Table from the Data Model, months are in FR no problem

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 18, 2023

    Hi Marco_RC 

     

    The "solution" I put together a couple of years ago is attached (zipped as this site doesn't accept .xltx)
    Let me know if anything isn't clear or if you have questions

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 18, 2023

    Hi Marco_RC 

     

    For your workbook created on Windows 10 US Pro with en-US regional format, just in case you did not think about it...

    Create on a sheet you'll hide later:


    For clarity let's assume the table that holds the month names in English in called Facts
    - Load MonthsES to the Data Model
    - Create a relationship between MonthsES (on [ID]) and Facts (field is probably [date (Month Index)]
    - On Facts add a calculated column [Month] with formula: =RELATED(MonthsES[Month])
    - Hide column [date (Month)] from the Client Tools
    - Remove [date (Month)] from your Pivot
    - Add [Month] instead

    (Implemented in attached file)

     

    BTW about DAX FORMAT function, forgot to mention I also tried the following some time ago. No error is raised but that doesn't have the expected effect: =FORMAT([date], "[$-es-MX]MMM")

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 17, 2023

    Hey Marco_RC 

     

    I was hoping to get the file created with Windows 10 US Pro not your test file. The latter doesn't help trying to fix the first 🙂

     

    Agreed on the FORMAT function... 😞

    Side note: a couple of days ago I experienced an issue with NATURALINNERJOIN that doesn't work in Excel DM but works no problem in PowerBI with the same input tables. According to my research the issue is "known" for a long time. A workaround exists but it's awful & complex from my perspective

     

    (Late my time) I'll provide "my" generic solution tomorrow (need to do a bit of clean-up + doc.). In any case users must be educated not to used any date based field(s) from their table(s) but to the PQ Calendar otherwise they'll always face the issue - or I missed something... (always possible)

     

    Regards