Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

date table with month name

I am creating a date table using DAX.

 

Every thing works except for the Month Names.  It is as if it is random. 

 

This is the same method for formatting date names as seen on a YouTube video. 

 

My first question on the Board. I couldn't find the answer this time.

 

Here is my DAX:

 

Dates_INV = GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 2 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
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 ),
"yearsort", INT( (nowYear-years)
)
))

3 Replies

  • ac-pbi's avatar
    ac-pbi
    Frequent Visitor

    Having a similar issue: Power BI is not picking up the correct month value, it's offset by a month which makes no sense:

    Open to ideas about how to solve!

    • Rudy_D's avatar
      Rudy_D
      Resolver I

      hey ac-pbi ,

       

      Format() function sees your Month number as Date. Number 1 converted to date format is 12/31/1899. Thats why you have Dec.

      You should use FORMAT() on your date column.

       

      Regards,

       

      Rudy

  • Hi,

     

    You need to apply FORMAT on Date. Please see my answer to the other post below.

    Regards,

     

    Rudy