Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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):
Thanks for yor help!
Bye,
Rudi
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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):
Thanks for yor help!
Bye,
Rudi
Hi, my first impression is the table is aggreating the months. Actual date is Unique row or several?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |