Thep Excel
CALENDARAUTOสร้างตารางวันที่อัตโนมัติจากช่วงวันที่ในโมเดล
CALENDARAUTO สร้างตารางวันที่อัตโนมัติโดยอิงช่วงวันที่ที่พบในโมเดล และสามารถกำหนดเดือนสิ้นสุดปีบัญชีได้ เหมาะกับการสร้าง Date table แบบเร็ว ๆ แต่ควรระวังค่าวันที่ผิดปกติในข้อมูล
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| [FiscalYearEndMonth]ไม่บังคับ | number | 12 | เดือนสิ้นสุดปีบัญชี (1-12) เช่น 3 หมายถึงสิ้นสุดมีนาคม หากไม่ระบุจะใช้ค่า 12 (ธันวาคม) หรือตัวตั้งค่าเริ่มต้นของแม่แบบปฏิทิน |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
CALENDARAUTO ใช้สร้างตารางวันที่แบบอัตโนมัติ โดยสแกนคอลัมน์วันที่ในโมเดล (ไม่รวม calculated columns/tables) เพื่อหาช่วงปีที่ครอบคลุมข้อมูล แล้วสร้างตารางวันที่ให้ครอบคลุมตั้งแต่วันแรกของปีบัญชีที่บรรจุ MinDate จนถึงวันสุดท้ายของปีบัญชีที่บรรจุ MaxDate เหมาะกับการเริ่มต้นทำ Time Intelligence แบบรวดเร็ว
ส่วนตัวผม CALENDARAUTO เป็นเหมือน “smart default” ของ DAX – เอาแล้วสร้างให้อย่างรวดเร็ว ปัญหาคือถ้าข้อมูลมี outlier วันที่ (เช่น ปีเก่ามากไม่คาดคิด) ตารางจะยาวเกินจำเป็น และสิ้นเปลือง storage ได้ 😎 เลยต้องตรวจสอบข้อมูลก่อน
ส่วนที่เจ๋งคือฟังก์ชันนี้เรียก CALENDAR ภายในตัว โดยคำนวณ MinDate/MaxDate อัตโนมัติจากข้อมูลที่นำเข้า ถ้าข้อมูลสะอาดก็ได้ตารางที่ดีในไม่กี่วินาที
How it works
สร้าง Date table แบบรวดเร็ว
เริ่มต้นทำโมเดลเวลาโดยไม่ต้องระบุช่วงเอง
รองรับปีบัญชี
กำหนดเดือนสิ้นสุดปีบัญชีเพื่อให้ตารางครอบคลุมช่วงปีบัญชีครบ
Examples
ตัวอย่างที่ 1: สร้างตารางวันที่อัตโนมัติ (ปฏิทินปกติ)Date Table = CALENDARAUTO()
เรียก CALENDARAUTO() โดยไม่ระบุ FiscalYearEndMonth จึงใช้ค่าเริ่มต้น 12 (ธันวาคม) ฟังก์ชันจะสแกนคอลัมน์วันที่ทั้งหมดในโมเดล หาค่า Min/Max แล้อสร้างตารางครอบคลุมปีเต็มดั้งเดิม
ตัวอย่างที่ 2: ปีบัญชีสิ้นสุดมีนาคมDate Table (Fiscal) = CALENDARAUTO(3)
กำหนด FiscalYearEndMonth = 3 เพื่อให้ช่วงปี (fiscal year) เริ่มต้นเมษายนและสิ้นสุดมีนาคม สำหรับองค์กรที่ใช้ปีบัญชีไม่ตรงกับปฏิทิน
ตัวอย่างที่ 3: เพิ่มคอลัมน์วิเคราะห์ด้วย ADDCOLUMNSDate Table (With Year) = VAR BaseCalendar = CALENDARAUTO() RETURN ADDCOLUMNS( BaseCalendar, "Year", YEAR([Date]), "Month", MONTH([Date]), "Quarter", ROUNDUP(MON…
CALENDARAUTO ส่งคืนตารางที่มีคอลัมน์ Date เพียงอย่างเดียว ใช้ ADDCOLUMNS เพิ่มคอลัมน์ช่วย (Year/Month/Quarter) สำหรับสนับสนุนรายงาน Time Intelligence
ตัวอย่างที่ 4: จัดการเมื่อมี Outlier วันที่Date Table (Safe) = VAR MinValidDate = DATE(2015, 1, 1) VAR MaxValidDate = TODAY() RETURN CALENDAR(MinValidDate, MaxValidDate)
เมื่อข้อมูลมีค่าวันที่ผิดปกติ ให้ใช้ CALENDAR แทน CALENDARAUTO เพื่อควบคุมช่วงเวลาให้เหมาะสม
FAQs
ทำไม CALENDARAUTO บางครั้งสร้างตารางยาวมาก?+
มักเกิดจากข้อมูลมีค่าวันที่หลุดช่วง (outlier) เช่น ปีเก่ามากไม่คาดคิด หรือมีข้อมูลทดสอบในอดีต ทำให้ช่วง MinDate/MaxDate ที่สแกนได้กว้างเกินจำเป็น ตรวจสอบข้อมูลด้วย MIN/MAX ก่อน หรือใช้ CALENDAR กับ hardcoded dates เพื่อควบคุมให้เหมาะสม
ควรใช้ CALENDAR หรือ CALENDARAUTO ดี?+
ถ้าต้องการควบคุมช่วงเวลาให้แน่นอนให้ใช้ CALENDAR (ระบุ start/end date โดยชัดเจน) แต่ถ้าต้องการเริ่มเร็วและข้อมูลวันที่ในโมเดลสะอาด CALENDARAUTO จะสะดวกกว่า โดยควรตรวจสอบช่วงเวลาที่สร้างขึ้นหลังจาก Load Model
ใช้ CALENDARAUTO ใน Calculated Column ได้หรือไม่?+
ทำได้ แต่ไม่แนะนำ เพราะ Calculated Column มี row context และ CALENDARAUTO ส่งคืนตารางที่ใช้ filter context ถ้าต้องหาค่า Date ในแต่ละแถวให้ใช้ฟังก์ชันอื่น เช่น [Date] หรือ RELATED()
CALENDARAUTO รองรับ DirectQuery หรือไม่?+
ไม่ รองรับเฉพาะ Import Mode เท่านั้น ใน DirectQuery ต้องสร้าง Date table จากแหล่งข้อมูลหรือใช้ CALENDAR ด้วย hardcoded dates
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ CALENDARAUTO จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CALENDAR ใช้สำหรับสร้างตารางวันที่ครบถ้วนตั้งแต่วันเริ่มต้นถึงวันสิ้นสุดโดยไม่มีช่องว่าง เหมาะสำหรับสร้างตารางวันที่โครงสร้างพื้นฐาน (Date Dimension) ที่ใช้ร่วมกับ Time Intelligence Function
DATE สร้างค่าวันที่จากตัวเลข Year/Month/Day แล้วคืนค่าเป็นชนิด datetime เหมาะกับการประกอบวันที่จากคอลัมน์แยกส่วนหรือสร้างวันที่อ้างอิง เช่น วันแรกของเดือน วันตัดยอด
MONTH ดึงเลขเดือน (integer 1-12) จากค่า datetime โดยรับ input เป็น datetime value, column reference, หรือ text representation ของวันที่ และคืนค่าตั้งแต่ 1 (มกราคม) ถึง 12 (ธันวาคม) ตาม Gregorian calendar ใช้สร้าง calculated column ใน Date table เพื่อเป็น sort column สำหรับชื่อเดือน และใช้ใน measure เพื่อสร้าง conditional logic กับ custom time intelligence calculations
ดึงปีจากวันที่ในรูปแบบตัวเลข 4 หลัก สำหรับวิเคราะห์ข้อมูลตามปี
DATESYTD คืนตารางวันที่ของช่วงตั้งแต่ต้นปีถึงวันที่สุดท้ายที่มองเห็นใน Filter Context ใช้เป็นตัวกรองเวลาใน CALCULATE สำหรับการคำนวณยอดสะสมตั้งแต่ต้นปี รองรับทั้งปีปฏิทินและปีบัญชี (Fiscal Year) ด้วย YearEndDate argument
SAMEPERIODLASTYEAR คืน table ของวันที่ที่เลื่อนย้อนกลับไป 1 ปีจากช่วงวันที่ใน filter context ปัจจุบัน ใช้สำหรับการเปรียบเทียบ Year-over-Year (YoY) โดยต้องการ date table ที่ครบถ้วนต่อเนื่องและควร mark เป็น date table ในโมเดล
.
มักใช้ร่วมกับ CALCULATE เพื่อคำนวณค่าของปีก่อนหน้าในช่วงเดียวกัน ทำให้ได้ผลลัพธ์แบบ apples-to-apples comparison ที่แม่นยำโดยไม่ต้อง hard-code ช่วงวันที่ เหมาะสำหรับ measures และ calculated columns แต่ไม่รองรับ DirectQuery mode และไม่แนะนำสำหรับ visual calculations
ADDCOLUMNS เป็นฟังก์ชัน table transformation ที่ใช้สำหรับเพิ่มคอลัมน์ใหม่ (Calculated Columns) เข้าไปในตารางที่มีอยู่เดิม โดยคำนวณค่าในแต่ละแถวผ่าน Row Context และส่งคืนตารางเดิมพร้อมคอลัมน์ใหม่ต่อท้าย
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่