Error making month name measure - HELP

[Cromero3000]

Hello Team!,

I'm new in Power Bi and I'm learning about measures. I started creating a Calendar table only with DAX but I'm having problem creating a column to show the month name. Let me show you what I've done:

So, I want to create a column #5 the same as #4 with the difference that I need to show the Month's name instead of Day's name, therefore the formula would be slightly the same as column 4 as follows:

Right?? but I doesn't work!, for some reason is not recognizing the "Calendar[Date]" column and is giving me the below error:

Why? why does this happens? I mean, why did it work on Column 4 but it doesn't work on Column 5? it is the same formula with different name. I can't even replicate Column#3, it is like the "Calendar[Date]" column doesn't exist because it is not recognizing it.

Please your help. Thanks in advance!!

[Ashish_Mathur · Accepted solution]

Hi,

Write that formula as a calculated column - not as a measure.

[Cromero3000]

@Ashish_Mathur , it worked! thank you!. So, If I have learned correctly, does that means that only the first column has to be a measure and the rest of them need to be "calculated column"?? why is that? I mean, if a calculated column is an internal measure, why can't I manually make a measure for each column? sorry, just trying to understand how this works 

Thank you

[Ashish_Mathur]

You are welcome.  Columns 2, 3 and 4 should defeinitely be calculated columns.  Column 3 formula should be

Day = DAY(Calendar[Date])

Hope this helps.

[Cromero3000]

Thank you @Ashish_Mathur , it worked both ways for column 3:

• Day of Month = 'Calendar'[Date].[Day]
• Day = DAY(Calendar[Date])

I'll stay with your suggestiong. I still don't get why it has to be a calculated column and not an independent measure.

Regards!

[v-xuding-msft]

Hi @Cromero3000 ,

When we create calculated columns, we could use the column from the original table directly. However, we need to use a function to get values when create measures.  Because measures need context. I suggest you to reference some documents to learn what differences between calculated columns and measures and how to choose them.

There is a blog about it. I think you could reference firstly.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/ 

Please mark the helpful answer as a solution. Then we are able to close the thread. More people will find the answer quickly and benefit here.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

Hello @Cromero3000 

Try changing your column 3 to be

Day Number = DAY ( 'Calendar'[Date] )

By using 'Calendar'[Date].[Day] you are getting into the auto time intelligence functions in PowerBI which you want to avoid (you end up with a date table for every column with dates) and that is why you are making your own calendar table.3

You can also make the table in one larger measure which makes it easier to use in another book (you just copy the code, go to the new book, hit new table and paste it in).

Calendar = 
VAR DateRange = CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2025, 12, 31 ) )

RETURN 
ADDCOLUMNS(
    DateRange,
    "Day of Week", WEEKDAY ( [Date] ),
    "Day Number", DAY ( [Date] ),
    "Name of Day", FORMAT ( [Date], "DDDD"),
    "Month Name", FORMAT ( [Date], "MMMM" )
)

You can add as many additional columns as you need all in this one measure.

[Cromero3000]

Thank you @jdbuchanan71 !, your formula worked great, the only difference is that is displaying weeks of the month and not every day of the month. 

I tried to make a minimal change as shown below:

Calendario = 
VAR DateRange = CALENDAR(DATE(2018,1,1), DATE(2025,12,31))

RETURN
ADDCOLUMNS(
    DateRange,
    "Day of Week", WEEKDAY([Date],2),
    "Day Number", DAY([Date]),
    "Day name", FORMAT([Date], "DDDD"),
    "Month name", FORMAT([Date], "MMMM"),
)

But is giving me the below error:

I don't get it, is practically the same as your formula and it gives me the error.

What am I missing? sorry, geting bold pulling out my hair in here.