Forum Discussion
DAX Format function on date not updating
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.
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")
2 Replies
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.
