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

Post a Comment

Popular posts from this blog

Create Calendar Table with Power Query

Image
How to create calendar table by using power query Step 1: Click on Get Data Step 2: Select Blank Query Step 3: Click on Advance Editor in the Home Tab of Power Query Editor Step 4: Remove the syntax and paste below mentioned syntax Syntax: Paste below mentioned syntax let     StartDate = #date(2024, 1, 1),     EndDate = #date(2024, 12, 31),     NumberOfDays = Duration.Days(EndDate - StartDate),     DateList = List.Dates(StartDate, NumberOfDays+1, #duration(1, 0, 0, 0)),     TableFromList = Table.FromList(DateList, Splitter.SplitByNothing()) in     TableFromList If you want a calendar for a different range, modify the StartDate and EndDate variables accordingly. Step 5: Click "Done" in the Advanced Editor. Step 6: You may want to add additional columns to your calendar table, such as Year, Month, Quarter, Day of Week, etc. Here's an example of how to add a Year column: In Power Query Editor, select the "Date" column. Go to the "Add Column" tab. Click
Read more

Calculate Sales for Last Year Month to date

Sameperiodlastyear till to date based on selection in year and month selection Problem:  If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed. For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date. If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022. Solution:  For this scenario you need to create Three Dax Measures Measure 1 = (This measure is to calculate current month sales till the date) CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) ) Explanation: CALCULATE : This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a
Read more