Create Calendar Table in Power BI using DAX Function

 Calendar Table using Dax

Syntax:

CalendarTable = ADDCOLUMNS ( CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "DayOfWeek", WEEKDAY([Date]), "DayOfMonth", DAY([Date]), "WeekdayName", FORMAT([Date], "dddd"), "IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()), "IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4),

TRUE(), FALSE()) )

Explanation:

CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31))

Creates a table with a single column containing dates from January 1, 2024, to December 31, 2024.

ADDCOLUMNS (

Adds new columns to the table created by the CALENDAR function.

"Year", YEAR([Date]),

Adds a column named "Year" that extracts the year from each date in the table.

"MonthNumber", MONTH([Date]),

Adds a column named "MonthNumber" that extracts the month number from each date.

"MonthName", FORMAT([Date], "MMMM"),

Adds a column named "MonthName" that formats the date to display the month name.

"Quarter", QUARTER([Date]),

Adds a column named "Quarter" that extracts the quarter from each date.

"DayOfWeek", WEEKDAY([Date]),

Adds a column named "DayOfWeek" that extracts the day of the week as a number (1 to 7).

"DayOfMonth", DAY([Date]),

Adds a column named "WeekdayName" that formats the date to display the weekday name.

"IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()),

Adds a column named "IsWeekend" that checks if the day is a weekend (Saturday or Sunday) and returns TRUE or FALSE.

"IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4),

TRUE(), FALSE())

Adds a column named "IsHoliday" that checks if the day is a holiday (January 1st or July 4th) and returns TRUE or FALSE.

This DAX code generates a comprehensive calendar table with various time-related columns that can be used for analytical purposes in tools like Power BI. You can customize it further based on your specific requirements and region-specific holidays.



Comments

Popular posts from this blog

CONTAINSTRING DAX

Solving the issue of "The UseRelationship() and CrossFilter() function may not be used when querying 'Table' because it is constrained by row-level security."

Calculate Sales for Last Year Month to date