cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DayName in Date_Calendar Table

Hi, someone shared this DAX code with me to create a Date_Calendar table in a PBI desktop file.

 

Can you help me modify the code to include "DayName" in the table, e.g. Monday, Tuesday, etc.

 

CODE:

Date_Calendar =
GENERATE (
CALENDAR(Date(2019,1,1),Date(2021,12,31)),
VAR startOfWeek = 1 // Where 1 is Monday and 7 is Sunday, 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 ) )
VAR fiscalyear = CONCATENATE("FY", IF(MONTH(currentDay) <=6, VALUE(FORMAT(currentDay,"YY")), VALUE(FORMAT(currentDay,"YY")) +1 ) )
VAR fiscalfullyear = IF(MONTH(currentDay) <=6, VALUE(FORMAT(currentDay,"YYYY")), VALUE(FORMAT(currentDay,"YYYY")) +1 )
 
VAR fiscalmonthOLD = CONCATENATE( SWITCH(month(currentDay),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December") , fiscalfullyear )
 
VAR fiscalmonth = CONCATENATE( SWITCH(month(currentDay),1,"January ",2,"February ",3,"March ",4,"April ",5,"May ",6,"June ",7,"July ",8,"August ",9,"September ",10,"October ",11,"November ",12,"December ") , years )
 

VAR fiscalquarter = CONCATENATE( fiscalyear , SWITCH(month(currentDay),7,"-Q1",8,"-Q1",9,"-Q1" ,10,"-Q2",11,"-Q2",12,"-Q2" ,1,"-Q3",2,"-Q3",3,"-Q3" ,4,"-Q4",5,"-Q4",6,"-Q4", blank() ) )


RETURN ROW (
"Fiscal Quarter", fiscalquarter,
"Fiscal Full Year", fiscalfullyear,
"Fiscal Year", fiscalyear, "Week", startOfWeek,
"day", days,
"month", months,
"year", years,
"Fiscal Month", fiscalmonth,
"fiscalmonthOLD",fiscalmonthOLD

// "day index", dayIndex,
// "week index", weekIndex,
// "month index", INT( (years - nowYear ) * 12 + months - nowMonth )
// "year index", INT( years - nowYear )
)
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper V
Helper V

Re: DayName in Date_Calendar Table

Never mind. I got it. Instead of "MMMM" I changed to "dddd". Thank you!

View solution in original post

6 REPLIES 6
Highlighted
Super User VI
Super User VI

Re: DayName in Date_Calendar Table

To get the full day name use = Format([Date], "dddd")

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper V
Helper V

Re: DayName in Date_Calendar Table

Hello, this code gave me the month, eg. "April". What would it be if I want the DAY NAME, e.g. Tuesday. Thanks!

Highlighted
Helper V
Helper V

Re: DayName in Date_Calendar Table

Never mind. I got it. Instead of "MMMM" I changed to "dddd". Thank you!

View solution in original post

Highlighted
Super User VI
Super User VI

Re: DayName in Date_Calendar Table

I typed MMMM by accident.  Posted and then came back to edit.  You must have seen it by then.

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: DayName in Date_Calendar Table

Create below Column:

DayName = Format(Table[DateColumn],"dddd")

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
Highlighted
Helper V
Helper V

Re: DayName in Date_Calendar Table

Hi Pat, is it possible to add it into the Date_Calendar DAX code instead?
I tried that, but when I put it in the Calendar, it says "Friday" for every day of the month, instead of "Friday", "Saturday", etc. 

When I used a Table visual to bring that field in, all the day names of the week show up, but I don't know why in the calendar it doesn't, so I'm wondering if it's just easier to add to the Date_Calendar creation code?
Thanks.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors