Thep Excel
ENDOFMONTHหาวันสิ้นเดือนสำหรับ Time Intelligence
ENDOFMONTH คืนตารางวันที่ของวันสิ้นเดือนตามปฏิทิน เหมาะกับ Semi-Additive Calculations เช่น ยอดคงเหลือ, Inventory, หรือ Closing Balance ที่ต้องการ snapshot ณ สิ้นเดือน ไม่ว่าจะมีข้อมูลจริงในวันนั้นหรือไม่
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| Dates | column | คอลัมน์วันที่จากตารางวันที่/ปฏิทิน หรือ Table Expression ที่คืนค่าวันที่คอลัมน์เดียว |
Additional Notes
ENDOFMONTH คืนค่าเป็นตารางที่มีวันสิ้นเดือนของบริบทวันที่ปัจจุบัน ใช้เป็น filter argument ใน CALCULATE เพื่อประเมินมาตรวัดที่วันสิ้นเดือน
.
ที่เจ๋งคือ ENDOFMONTH จะคืนค่าวันที่ปฏิทิน (Calendar Date) เสมอ เช่น 31 มกราคม, 28/29 กุมภาพันธ์ ไม่ว่าจะมีข้อมูลจริงในวันนั้นหรือไม่ก็ตาม ซึ่งต่างจาก LASTDATE ที่คืนเฉพาะวันสุดท้ายที่มีข้อมูลจริง
.
ฟังก์ชันนี้สำคัญมากสำหรับ Semi-Additive Calculations เช่น ยอดคงเหลือสินค้า, ยอดเงินในบัญชี, จำนวนพนักงาน ที่ต้องดู “snapshot ณ สิ้นเดือน” แทนที่จะรวมทุกวัน
.
ส่วนตัวผมใช้ ENDOFMONTH คู่กับ CALCULATE บ่อยมากในงาน Financial Reporting เพราะมันรับประกันว่าจะได้วันสิ้นเดือนจริงๆ แม้ข้อมูลจะไม่ครบทุกวันก็ตาม 😎
How it works
Closing Balance / ยอดคงเหลือสิ้นเดือน
ดูยอดเงินในบัญชี สินค้าคงคลัง หรือค่าใดๆ ณ วันสิ้นเดือน
Semi-Additive Calculations
ค่าที่ไม่ควรรวมข้ามเวลา เช่น Balance, Headcount, Inventory ต้องดูเป็น snapshot
Monthly Snapshot Reporting
รายงานที่ต้องการค่า ณ สิ้นเดือนทุกเดือน เช่น Financial Statement
Opening vs Closing Balance Comparison
เปรียบเทียบยอดต้นเดือน-ปลายเดือนเพื่อหา Growth หรือ Movement
Examples
ตัวอย่างที่ 1: Closing Balance พื้นฐานClosing Balance = CALCULATE( SUM(Balances[Amount]), ENDOFMONTH('Date'[Date]) )
สมมติเลือกเดือน มกราคม 2024 → ENDOFMONTH จะคืน 31 ม.ค. 2024
CALCULATE จะกรองให้เหลือเฉพาะแถวที่ตรงกับวันที่ 31 ม.ค. แล้วรวม Amount
Pattern นี้เหมาะกับข้อมูลที่บันทึกยอดคงเหลือทุกวัน เช่น Bank Balance, Stock Level
ตัวอย่างที่ 2: Inventory at Month EndInventory Month End = CALCULATE( SUM(Inventory[Quantity]), ENDOFMONTH('Date'[Date]) )
สมมติตาราง Inventory มี Quantity ทุกวัน:
– 28 ม.ค.: 100 ชิ้น
– 29 ม.ค.: 95 ชิ้น
– 30 ม.ค.: 88 ชิ้น
– 31 ม.ค.: 92 ชิ้น
ผลลัพธ์ = 92 ชิ้น (เฉพาะวันที่ 31)
ที่ต้องระวังคือ ถ้าไม่มีข้อมูลวันที่ 31 ผลลัพธ์จะเป็น BLANK เพราะ ENDOFMONTH คืนวันปฏิทินเสมอ
ตัวอย่างที่ 3: LASTDATE vs ENDOFMONTH (ความแตกต่างสำคัญ!)— ถ้าข้อมูลมีถึงแค่วันที่ 28 ม.ค. — LASTDATE จะคืน 28 ม.ค. (วันสุดท้ายที่มีข้อมูล) Balance LastDate = CALCULATE( SUM(Balances[Amount]), LASTDATE('Date'[Date])…
นี่คือความแตกต่างสำคัญมาก!
🔹 LASTDATE = วันสุดท้ายที่มีข้อมูลจริง (Last Date with Data)
🔹 ENDOFMONTH = วันสิ้นเดือนตามปฏิทิน (Calendar Month End)
ใช้ LASTDATE เมื่อ: ข้อมูลอาจไม่ครบทุกวัน แต่ต้องการยอดล่าสุด
ใช้ ENDOFMONTH เมื่อ: ต้องการยอด ณ วันสิ้นเดือนจริงๆ เท่านั้น 💡
ENDOFMONTH = BLANK (ไม่มีข้อมูลวันที่ 31)
ตัวอย่างที่ 4: Opening vs Closing Balance— Closing Balance (ยอดปลายเดือน) Closing Balance = CALCULATE( SUM(Balances[Amount]), ENDOFMONTH('Date'[Date]) ) — Opening Balance (ยอดต้นเดือน = ยอดปลายเดือนก…
Pattern สำคัญสำหรับ Financial Reporting:
🔹 Closing Balance = ENDOFMONTH ของเดือนปัจจุบัน
🔹 Opening Balance = ENDOFMONTH ของเดือนก่อน = วันก่อน STARTOFMONTH
DAXPatterns.com แนะนำ: ใช้ DATEADD(FIRSTDATE/STARTOFMONTH, -1, DAY) เพื่อหา Opening Balance
หรือใช้ OPENINGBALANCEMONTH / CLOSINGBALANCEMONTH ที่ Microsoft สร้างมาให้แล้ว 😎
Opening = ยอด ณ 31 ธ.ค. (เดือนก่อน)
ตัวอย่างที่ 5: Monthly Snapshot ReportMonthly Snapshot = VAR ClosingBalance = CALCULATE( SUM(Accounts[Balance]), ENDOFMONTH('Date'[Date]) ) VAR OpeningBalance = CALCULATE( SUM(Accounts[Balance]), DA…
Pattern ครบสำหรับ Monthly Financial Report:
1. Closing Balance = ENDOFMONTH
2. Opening Balance = วันก่อน STARTOFMONTH (= สิ้นเดือนก่อน)
3. Movement = Closing – Opening
ใช้ VAR เพื่อคำนวณค่ากลางก่อน แล้ว RETURN ค่าที่ต้องการ
ส่วนตัวผมใช้ Pattern นี้ทำ Cash Flow Statement และ Balance Sheet Movement Analysis 💡
ตัวอย่างที่ 6: Growth CalculationBalance Growth = VAR Opening = CALCULATE( SUM(Balances[Amount]), DATEADD(STARTOFMONTH('Date'[Date]), -1, DAY) ) VAR Closing = CALCULATE( SUM(Balances[Amount]),…
Pattern จาก DAXPatterns.com สำหรับหา Growth:
1. คำนวณ Opening และ Closing
2. เช็คว่าทั้งสองค่าไม่เป็น BLANK
3. คำนวณ Delta (Closing – Opening)
4. Return เฉพาะเมื่อ Delta ไม่เท่ากับ 0
ที่เจ๋งคือ Pattern นี้ handle edge case ได้ดี ไม่ return ค่าผิดๆ เมื่อข้อมูลไม่ครบ
FAQs
ENDOFMONTH vs LASTDATE ต่างกันยังไง?+
ENDOFMONTH คืนวันสิ้นเดือนตามปฏิทินเสมอ (เช่น 31, 30, 28/29) ไม่ว่าจะมีข้อมูลหรือไม่
LASTDATE คืนวันสุดท้ายที่มีข้อมูลจริงใน filter context
ตัวอย่าง: ถ้าข้อมูลมกราคมมีถึงแค่วันที่ 25
– ENDOFMONTH → 31 ม.ค. (อาจได้ BLANK ถ้าไม่มีข้อมูล)
– LASTDATE → 25 ม.ค. (ได้ยอดวันสุดท้ายที่มี)
ใช้ ENDOFMONTH เมื่อต้องการวันปฏิทินแน่นอน
ใช้ LASTDATE เมื่อต้องการยอดล่าสุดที่มีจริงๆ
ทำไม ENDOFMONTH คืนค่าเป็นตาราง ไม่ใช่วันที่?+
ENDOFMONTH ถูกออกแบบมาเพื่อใช้เป็น filter argument ใน CALCULATE โดยคืนตาราง 1 แถว 1 คอลัมน์
DAX จะแปลงเป็น scalar อัตโนมัติเมื่อต้องการ แต่ถ้าต้องการแค่ค่าวันที่ ใช้ EOMONTH หรือ MAX + EOMONTH จะเร็วกว่า
ตาม SQLBI แนะนำ: ใช้ ENDOFMONTH/LASTDATE เป็น filter ใน CALCULATE เท่านั้น ถ้าต้องการค่า scalar ให้ใช้ MAX/MIN แทน 💡
Semi-Additive Calculation คืออะไร?+
ค่าที่ไม่ควรรวมข้ามเวลา เช่น:
– Bank Balance: ยอด 1 ม.ค. = 1000, ยอด 2 ม.ค. = 1200 → รวมแล้วได้ 2200 ซึ่งไม่มีความหมาย!
– Inventory: จำนวนสินค้าในคลังแต่ละวัน รวมกันไม่ได้
– Headcount: จำนวนพนักงานแต่ละวัน
ค่าพวกนี้ต้องดูเป็น "snapshot" ณ จุดใดจุดหนึ่ง ไม่ใช่รวมกัน
Pattern: ใช้ ENDOFMONTH/LASTDATE เพื่อดูค่า ณ จุดเวลาที่ต้องการ 😎
Opening Balance หาอย่างไร?+
Opening Balance ของเดือนปัจจุบัน = Closing Balance ของเดือนก่อน
วิธีที่ 1: ใช้ DATEADD
Opening = CALCULATE([Balance], DATEADD(STARTOFMONTH('Date'[Date]), -1, DAY))
วิธีที่ 2: ใช้ Built-in Function
Opening = OPENINGBALANCEMONTH(SUM(Balances[Amount]), 'Date'[Date])
ทั้งสองวิธีให้ผลเหมือนกัน วิธีที่ 2 อ่านง่ายกว่า 💡
ENDOFMONTH ทำงานกับ Fiscal Calendar ได้ไหม?+
ได้ครับ! ใช้กับ Calendar Reference:
ENDOFMONTH(FiscalCalendar)
จะคืน tagged primary columns ที่ตรงกับสิ้นเดือนของ Fiscal Calendar
แต่ต้องตั้งค่า Calendar ให้ถูกต้องก่อนนะครับ
ใช้ ENDOFMONTH ใน Visual Calculation ได้ไหม?+
ทำได้ แต่ Microsoft แนะนำว่าไม่ควรใช้ เพราะ "likely returns meaningless results" ในบริบทของ Visual Calculation
Time Intelligence functions ควรใช้ใน Measure เท่านั้นครับ 😅
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ENDOFMONTH จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CLOSINGBALANCEMONTH ประเมิน Expression ณ วันสิ้นเดือนของบริบทวันที่ปัจจุบัน เหมาะกับการทำมาตรวัดยอดคงเหลือปลายเดือน และรองรับการใส่ตัวกรองเพิ่มเติม
DATEADD คืนค่าตารางวันที่ ที่ถูกเลื่อนออกไปจากช่วงวันที่ใน Context ปัจจุบัน ตามจำนวน (NumberOfIntervals) และหน่วย (Interval) ที่กำหนด เช่น เลื่อนย้อนหลัง 1 เดือน หรือเลื่อนไปข้างหน้า 1 ปี
DATESMTD คืนตารางวันที่ของช่วงตั้งแต่ต้นเดือนถึงวันที่ล่าสุดใน filter context เหมาะกับการทำยอดสะสมตั้งแต่ต้นเดือน (MTD) โดยใช้ร่วมกับ CALCULATE และควรใช้คู่กับ ShowValueForDates pattern เพื่อไม่ให้แสดงค่าในวันที่ยังไม่มีข้อมูล
ENDOFQUARTER คืนตารางวันที่ของวันสิ้นไตรมาสตามบริบทวันที่ปัจจุบัน เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อประเมินมาตรวัด ณ วันสิ้นไตรมาส
ฟังก์ชัน ENDOFWEEK คืนตารางวันที่ที่แสดงวันสิ้นสุดของสัปดาห์ตามบริบทวันที่ปัจจุบัน ใช้สำหรับการวิเคราะห์ข้อมูลตามระดับสัปดาห์ และการเปรียบเทียบเมตริกส์ณวันปิดสัปดาห์
ENDOFYEAR คืนตารางวันที่ของวันสิ้นปีตามบริบทวันที่ปัจจุบัน และรองรับการกำหนดวันสิ้นปีบัญชี เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อประเมินมาตรวัดที่วันสิ้นปี
FIRSTDATE คืนค่าวันที่แรกในบริบทตัวกรองปัจจุบัน เหมาะใช้เป็นตัวกรองให้ CALCULATE เพื่อหายอดขาย/จำนวนของวันแรกในช่วงที่เลือก
LASTDATE คืนค่าตารางที่มีวันที่สุดท้ายในบริบทตัวกรองปัจจุบัน มักใช้เป็นตารางตัวกรองให้ CALCULATE เพื่อคำนวณ measure ณ วันสุดท้ายของช่วงที่เลือก และสามารถใช้ MAXX เพื่อแปลงผลเป็น scalar date ได้
OPENINGBALANCEMONTH ประเมิน Expression ณ จุดเปิดเดือน (วันสิ้นเดือนก่อนหน้า) เหมาะสำหรับหา opening balance ของยอดคงเหลือ สต็อก หรือสถิติ ณ จุดเริ่มเดือน
STARTOFMONTH คืนวันแรกของเดือนเป็นตาราง 1 แถว ใช้กับการวัดที่ขึ้นต่อจุดเวลา เช่น ยอดคงเหลือ บัญชี หรือสินค้าคงคลัง
คำนวณยอดสะสมตั้งแต่ต้นเดือน (Month-to-Date) ของ Expression ตามคอลัมน์วันที่ที่กำหนด เป็นทางลัดของ CALCULATE + DATESMTD
CALCULATE เป็นฟังก์ชันหลักที่สำคัญที่สุดใน DAX ใช้สำหรับ evaluate expression ภายใต้ filter context ที่ถูกปรับเปลี่ยน สามารถเพิ่มตัวกรองใหม่ ลบตัวกรองเดิม หรือแทนที่ filter ที่มีอยู่ได้ รองรับทั้ง Boolean expression และ table expression เป็น filter arguments พร้อม filter modifier functions เช่น REMOVEFILTERS, ALL, ALLEXCEPT, KEEPFILTERS เพื่อควบคุมการกรองอย่างละเอียด มีพฤติกรรมพิเศษคือ context transition ที่เปลี่ยน row context เป็น filter context โดยอัตโนมัติ ทำให้เป็นเครื่องมือหลักในการสร้าง measure ที่ซับซ้อนและ calculated column ที่ต้องใช้ aggregation
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่