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), I see various results and sometimes even the correct month number.

Since this is hard to explain, I have attached the snapshot of some tests I did. The numbers do not seems fully random, but they are definitely odd looking.

 

Anyone?

 

Rudi

 

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

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    rudiklein

     

    Hi, my first impression is the table is aggreating the months. Actual date is Unique row or several?

    • rudiklein's avatar
      rudiklein
      Advocate III
      9 years ago

      Hi Vvelarde,

       

      You are right. The month numbers that came from the table were summed. The field was set to "SUM". I overlook that.

      That partially fixed my example table. Some of the fields were still strange ;looking, but that is explaned in my reply to MFelix.

       

      Thanks for your help.

       

      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