Calendar Made with DAX in Direct Query file: Month Name not working

[Anonymous]

 I have built a file with a direct query to the data warehouse. I created a Calendar table. Month number is fine but Month names are not. I made the table by clicking the <Table Tools> New table icon. 

[Snippet of where I entered the code]

Here is the code. Everything else appears to be working good. 

BI_Calendar = GENERATE (

CALENDAR( DATE( YEAR( TODAY() ) - 5, MONTH( TODAY() -1 ), DAY( TODAY()) -1 ), TODAY() -1 ),

VAR startOfWeek = 2 // Where 1 is Sunday so this returns Monday as Week Start

VAR currentDay = [Date]

VAR days = DAY( currentDay )

VAR months = MONTH ( currentDay )

VAR years = YEAR ( currentDay )

VAR nowYear = YEAR( TODAY() )

VAR nowMonth = MONTH( TODAY() )

VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

VAR todayNum = WEEKDAY( TODAY() )

VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

RETURN ROW (

"day", days,

"month", months,

"MonthNameShort", FORMAT ( months, "mmm" ),

"MonthNameLong", FORMAT ( months, "mmmm" ),

"year", years,

"day index", dayIndex,

"week index", weekIndex,

"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

"year index", INT( years - nowYear )

)

)

I've used this calendar about 18 months ago in a different place and it worked fine. Outdated maybe?

Thank you much and I give Kudos! 

[calerof · Accepted solution]

Hi @Anonymous ,

Try this small change:

"MonthNameShort", FORMAT ( [Date], "mmm" )

Cheers,

Fernando 

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

For my test, the first parameter in FORMAT() should be the whole date value not a single month number.

In your case, replace the "month" with "currentDay", then the month name will be correct.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@Anonymous , I think typically generate calendar using add columns on top of Calendar

example

First Date =
var Weekstart = 2
return
ADDCOLUMNS (
CALENDAR ( DATE ( 2018, 01, 01 ), DATE ( 2020, 12, 31 ) ),
"Month Year", FORMAT ( [Date], "mmm-yyyy" ),
"Month Year sort", FORMAT ( [Date], "yyyymm" ),

"Month", FORMAT ( [Date], "mmm" ),

"Month Sort", FORMAT ( [Date], "mm" ),
"Year", YEAR ( [Date] )
)

You can add other columns as per need. For FY and Week of choice refer my blogs

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

[calerof · Accepted solution]

Hi @Anonymous ,

Try this small change:

"MonthNameShort", FORMAT ( [Date], "mmm" )

Cheers,

Fernando