month name not working by using format function in power bi

2
votes

I have used the following to get the stating day of the month

multiemp[Day] - WEEKDAY(multiemp[Day],2)

For example if my date us 22 May 2018, after using the above query, got the expected out put.i.e., 20 May 2018

Now I tried to get the month name by using the above query and format function

format(month(multiemp[Day] - WEEKDAY(multiemp[Day],2)),"mmm")

results were not expected, instead of may January is getting populated.

when verify with the following month function expected results arrived .i.e, 5

month(multiemp[Day] - WEEKDAY(multiemp[Day],2)

but only issue in changing the month number to month name

Please find below:

enter image description here

formulas used

weekstartday = multiemp[Day] - WEEKDAY(multiemp[Day],2)
weekstartday_month = month(multiemp[weekstartday])
Month_name = format(multiemp[weekstartday_month],"mmm")
another_ans = format(dateadd(multiemp[Day],-weekday(multiemp[Day],2),day),"mmm")
another_answer_date = dateadd(multiemp[Day],-weekday(multiemp[Day],2),day)

EDIT: Day 2 Modified the datatype of the column to date time/timezone.

enter image description here

after refresh the data didn't change

enter image description here

Found out the solution

Solution 1:

mnname = format(multiemp[weekstartday].[Date],"mmm")

Solution 2:

Month_name = format(date(YEAR(multiemp[weekstartday]),MONTH(multiemp[weekstartday]),day(multiemp[weekstartday])),"mmm")

Thanks in advance

3
powerbidaxpowerbi-desktop
If is the case you needed urgently you can use switch(...) to get the month name. But the right thing is to find out the reason why format(...) is not working as expected in your case. – SNR

3 Answers

3
votes

Format the column instead of repeating the column DAX statement.

Column = FORMAT(*nameOfYourStartOfWeekColumn*,"mmm")

enter image description here

1
votes

Because FORMAT(...,"MMM") or FORMAT(...,"MMMM") takes as an argument a date types and non numeric types, try this

format(dateadd(multiemp[Day],-weekday(multiemp[Day],2),day),"MMM")

If you want the starting day of the week as monday, then,

format(dateadd(multiemp[Day],-weekday(multiemp[Day],3),day),"MMM")

EDIT

Verify that date column is date or date\time type.

Date_data_type

0
votes

Solution 1:

mnname = format(multiemp[weekstartday].[Date],"mmm")

Solution 2:

Month_name = format(date(YEAR(multiemp[weekstartday]),MONTH(multiemp[weekstartday]),day(multiemp[weekstartday])),"mmm")