Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

 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]

ScubaBob_0-1600892981393.png

 

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! 
2 ACCEPTED SOLUTIONS
calerof
Impactful Individual
Impactful Individual

Hi @Anonymous ,

 

Try this small change:

 

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

 

Cheers,

 

Fernando 

View solution in original post

v-eqin-msft
Community Support
Community Support

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.

9.24.2.1.png

 

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

View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

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.

9.24.2.1.png

 

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
Super User
Super User

@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
Impactful Individual
Impactful Individual

Hi @Anonymous ,

 

Try this small change:

 

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

 

Cheers,

 

Fernando 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors