Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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]

 

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! 
Need Help
  • calerof's avatar
    calerof
    6 years ago

    Hi Anonymous ,

     

    Try this small change:

     

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

     

    Cheers,

     

    Fernando 

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • calerof's avatar
    calerof
    Icon for Impactful Individual rankImpactful Individual
    6 years ago

    Hi Anonymous ,

     

    Try this small change:

     

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

     

    Cheers,

     

    Fernando 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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