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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Tahreem24
Super User
Super User

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!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
mahoneypat
Employee
Employee

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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.

Anonymous
Not applicable

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

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.