Forum Discussion

rudiklein's avatar
rudiklein
Advocate III
9 years ago
Solved

MONTH() function shows peculiar results

Hi,   I'm wondering if I'm using the MONTH() function in the right way. The reason why I'm asking is the strange results I see when I use the function. Rather than showing the month number (1...12)...
Need Help
  • rudiklein's avatar
    rudiklein
    9 years ago

    Hi MFelix,

     

    Yes, the PBI Regional settings do influence the outcome of dates, but not in this case. I have changed to Regional setting from Dutch to English UK and the outcome of 2a,b,c,d were the same. It seems Month() will take any date, with or without double quotes ("), but it will provide a correct answer when in quotes. However it seems to interpret the Dutch date "8-2-2017" (8th of February) as the 2nd of August, similar to the UK/US format "8/2/2017".

     

    What it calculates in 2a and 2b I don't know.

     

    3 and 4 work if you use the [Actual date] in the example without the [.Month] and use "MMM" and "MM" as format strings.

     

    Lessons learned here (at least for me):

    • Be aware when using MONTH() and FORMAT() with dates. They will accept any date format, but the outcome is not always what you expect.
    • Check the field calculation settings and make sure it is set to "Don't summarize".
    • And last: beware of the Regional settings in PBI Desktop.  

    Thanks for yor help!

     

    Bye,

     

    Rudi

MFelix's avatar
MFelix
Super User
9 years ago

Hi rudiklein,

 

What is the format you have for the Power BI (File -> Options -> Regional Settings), I believe that this different results are caused by the different input in the formulas.

 

Making a month from text "2-8-2017" is different from "8-2-2017" depending on your regional settings it assumes diferent things, this is as also happens with the excel files. Check if the Actual Date is in the correct format in the query editor also.

 

Regards,

 

MFelix

  • rudiklein's avatar
    rudiklein
    Advocate III
    9 years ago

    Hi MFelix,

     

    Yes, the PBI Regional settings do influence the outcome of dates, but not in this case. I have changed to Regional setting from Dutch to English UK and the outcome of 2a,b,c,d were the same. It seems Month() will take any date, with or without double quotes ("), but it will provide a correct answer when in quotes. However it seems to interpret the Dutch date "8-2-2017" (8th of February) as the 2nd of August, similar to the UK/US format "8/2/2017".

     

    What it calculates in 2a and 2b I don't know.

     

    3 and 4 work if you use the [Actual date] in the example without the [.Month] and use "MMM" and "MM" as format strings.

     

    Lessons learned here (at least for me):

    • Be aware when using MONTH() and FORMAT() with dates. They will accept any date format, but the outcome is not always what you expect.
    • Check the field calculation settings and make sure it is set to "Don't summarize".
    • And last: beware of the Regional settings in PBI Desktop.  

    Thanks for yor help!

     

    Bye,

     

    Rudi