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.
I'm using the below formula in an attempt to extract the month and year from the data view:
Date_Mnth_Yr = FORMAT(MONTH([Date]),"mmm")&"-"&YEAR([Date])
However the formula isn't updating for all the rows, instead using the same formatted value across all rows.
Solved! Go to Solution.
Can you show your a sample where the results are not what you expect? I suspect it's the formula you're using. The current formula formats the month number as a month, not the date's month.
For example, if 1 July 2016 is your Date, the current formula will return Jan-2016. It provides the month number, 7, to be formatted as mmm. Trying to pull the month from 7 is the same as pulling the month from 7 Jan 1900, the number 7 converted to a date.
If you change your formula to FORMAT([Date], "mmm") & "-" & YEAR([Date]), you'd end up with Jul-2016 from 1 July 2016. You could also use
Date_Mnth_Yr = FORMAT([Date], "mmm-yyyy")
Can you show your a sample where the results are not what you expect? I suspect it's the formula you're using. The current formula formats the month number as a month, not the date's month.
For example, if 1 July 2016 is your Date, the current formula will return Jan-2016. It provides the month number, 7, to be formatted as mmm. Trying to pull the month from 7 is the same as pulling the month from 7 Jan 1900, the number 7 converted to a date.
If you change your formula to FORMAT([Date], "mmm") & "-" & YEAR([Date]), you'd end up with Jul-2016 from 1 July 2016. You could also use
Date_Mnth_Yr = FORMAT([Date], "mmm-yyyy")
Thanks, that is exactly what was happening. The solution you provided however did the trick.
@KGrice wrote:For example, if 1 July 2016 is your Date, the current formula will return Jan-2016. It provides the month number, 7, to be formatted as mmm. Trying to pull the month from 7 is the same as pulling the month from 7 Jan 1900, the number 7 converted to a date.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |