Thep Excel
CALENDAR – สร้างตารางวันที่โดยอัตโนมัติระหว่างวันเริ่มต้นและสิ้นสุด
CALENDAR ใช้สำหรับสร้างตารางวันที่ครบถ้วนตั้งแต่วันเริ่มต้นถึงวันสิ้นสุดโดยไม่มีช่องว่าง เหมาะสำหรับสร้างตารางวันที่โครงสร้างพื้นฐาน (Date Dimension) ที่ใช้ร่วมกับ Time Intelligence Function
=CALENDAR(<start_date>, <end_date>)
Function Metrics
Popularity
7/10
Difficulty
4/10
Usefulness
8/10
Syntax & Arguments
=CALENDAR(<start_date>, <end_date>)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| start_date | DateTime | Yes | วันที่เริ่มต้น ตัวอักษร หรือ expression ที่คืนค่า datetime | |
| end_date | DateTime | Yes | วันที่สิ้นสุด ตัวอักษร หรือ expression ที่คืนค่า datetime |
How it works
สร้าง Date table แบบกำหนดช่วงเอง
เช่น ต้องการให้ครอบคลุมปี 2020-2030 โดยไม่ขึ้นกับข้อมูลธุรกรรม
สร้าง Date table จากช่วงวันที่ในข้อมูล
เช่น ใช้วันแรกสุด/วันสุดท้ายสุดจากคอลัมน์วันที่ในข้อมูลธุรกรรม
Examples
สร้างตารางวันที่แบบง่ายๆ
Calendar Table = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))
Calendar Table = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))
ตารางที่มีวันที่ตั้งแต่ 1 ม.ค. 2024 ถึง 31 ธ.ค. 2024 (365 แถว)
สร้างตารางวันที่แบบไดนามิก (Dynamic Range)
Calendar Table = CALENDAR( DATE(YEAR(MIN(Sales[OrderDate])), 1, 1), DATE(YEAR(MAX(Forecast[ForecastDate])), 12, 31) )
Calendar Table = CALENDAR(
DATE(YEAR(MIN(Sales[OrderDate])), 1, 1),
DATE(YEAR(MAX(Forecast[ForecastDate])), 12, 31)
)
ตารางวันที่ตั้งแต่วันแรกของปีข้อมูล Sales ถึงวันสุดท้ายของปี Forecast
เพิ่มคอลัมน์ Year, Month, Quarter ด้วย ADDCOLUMNS
Calendar Table = ADDCOLUMNS( CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"),…
Calendar Table =
ADDCOLUMNS(
CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & ROUNDUP(MONTH([Date])/3, 0)
)
ตารางที่มี 5 คอลัมน์: Date, Year, Month, MonthName, Quarter
สร้างตารางวันที่พร้อมกับการหา Min/Max จากข้อมูลหลายตาราง
Calendar Table = VAR StartDate = DATE(YEAR(MINX(Sales, Sales[OrderDate])), 1, 1) VAR EndDate = DATE(YEAR(MAXX(UNION(Sales, Forecast), COLUMN)), 12, 31) RETURN C…
Calendar Table =
VAR StartDate = DATE(YEAR(MINX(Sales, Sales[OrderDate])), 1, 1)
VAR EndDate = DATE(YEAR(MAXX(UNION(Sales, Forecast), COLUMN)), 12, 31)
RETURN
CALENDAR(StartDate, EndDate)
ตารางวันที่ที่ครอบคลุมทั้งข้อมูล Sales และ Forecast อย่างสมบูรณ์
สร้าง Date Table พร้อมทั้งหา Last Date ที่มีข้อมูล
Calendar Table = VAR MinDate = MINX(Sales, Sales[OrderDate]) VAR MaxDate = MAXX(Sales, Sales[OrderDate]) RETURN CALENDAR( DATE(YEAR(MinDate), 1, 1), DATE(YEAR(M…
Calendar Table =
VAR MinDate = MINX(Sales, Sales[OrderDate])
VAR MaxDate = MAXX(Sales, Sales[OrderDate])
RETURN
CALENDAR(
DATE(YEAR(MinDate), 1, 1),
DATE(YEAR(MaxDate), 12, 31)
)
ตารางวันที่ปีเต็มที่ครอบคลุมข้อมูล Sales ตั้งแต่ต้นปีที่มีข้อมูลถึงสิ้นปีที่มีข้อมูล
FAQs
CALENDAR กับ GENERATESERIES ต่างกันอย่างไร?
CALENDAR ทำงานกับ Date แต่ GENERATESERIES ทำงานกับตัวเลข ผลลัพธ์เหมือนกันแต่ CALENDAR ส่งคืน Date type ส่วน GENERATESERIES ส่งคืน Numeric ส่วนตัวผมชอบ CALENDAR สำหรับ Date Table เพราะมันเสร็จสรรค์กว่า
ทำไมต้องเพิ่มคอลัมน์ Year, Month ทั้งๆ ที่ Date เก็บข้อมูลเหล่านั้นแล้ว?
วิธีนี้ดีเพราะ Time Intelligence Function ทำงานได้เร็วกว่า และ Slicer ใน Visual ง่ายต่อการใช้งาน เช่นลักษณ์ “Dec 2024” ชัดเจนกว่า “2024-12-31 00:00:00”
CALENDAR จำนวนแถวสูงสุดเท่าไหร่?
ไม่มีข้อจำกัด แต่ถ้าช่วงวันที่ยาวเกินไป (เช่น 100 ปี) อาจส่งผลต่อ Performance เล็กน้อย ส่วนตัวผมแนะนำสร้าง Date Table ให้ครอบคลุมข้อมูลเท่านั้น ไม่ต้อง Overkill
ถ้า start_date > end_date จะเกิดอะไร?
มันจะคืนค่า Error เจอบ่อยครับ 😅 ต้องระวังให้ start_date น้อยกว่า end_date เสมอ หากไม่แน่ใจให้ใช้ MIN() กับ MAX() ก่อน
CALENDAR รองรับ DirectQuery ไหม?
ไม่รองรับในโหมด DirectQuery สำหรับ Calculated Column หรือ RLS แต่ใช้ได้ใน Calculated Table ปกติ นี่เป็นข้อจำกัด ส่วนตัวผมแนะนำใช้ Import Mode แทน
Resources & Related
Resources
-
Microsoft Learn: CALENDAR
official -
DAX Guide: CALENDAR
guide -
SQLBI: Date Tables in DAX
guide
Related functions
Additional Notes
CALENDAR เป็นฟังก์ชันที่ทำงานเรียบง่ายแต่มีประโยชน์มากในการสร้างโครงสร้างข้อมูลเวลา คุณส่งวันที่เริ่มต้นและสิ้นสุด มันจะคืนตารางที่มีคอลัมน์เดียวชื่อ “Date” พร้อมวันที่ทุกวันในช่วงนั้น
ที่เจ๋งคือมันสร้างวันที่ที่ต่อเนื่องกันอย่างสมบูรณ์ ไม่มีช่องว่างเลย ซึ่งสำคัญมากสำหรับฟังก์ชัน Time Intelligence เช่น SAMEPERIODLASTYEAR ที่ต้องการให้ Date Table มีวันที่ครบถ้วน
ส่วนตัวผมใช้ CALENDAR ในเกือบทุกโปรเจคเพื่อสร้างตารางวันที่พื้นฐาน แล้วเพิ่มคอลัมน์อื่นๆ เช่น Year, Month, Quarter ด้วย ADDCOLUMNS หรือ Calculated Column 😎