Thep Excel
CLOSINGBALANCEYEAR – ยอดคงเหลือสิ้นปี
ฟังก์ชันเวลา Intelligence ที่ประเมิน Expression ณ วันสิ้นปีของปีในบริบทปัจจุบัน รองรับปีบัญชี (Fiscal Year) และตัวกรองเพิ่มเติม เหมาะสำหรับมาตรวัด Semi-additive เช่น ยอดคงเหลือ ยอดเงินสด
=CLOSINGBALANCEYEAR(<Expression>, <Dates>[, <Filter>][, <YearEndDate>])
Function Metrics
Popularity
5/10
Difficulty
5/10
Usefulness
5/10
Syntax & Arguments
=CLOSINGBALANCEYEAR(<Expression>, <Dates>[, <Filter>][, <YearEndDate>])
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| Expression | scalar | Yes | นิพจน์หรือมาตรวัดที่ต้องการประเมิน เช่น [Balance], [Cash], SUMX(…) มันสามารถเป็นฟังก์ชันหรือมาตรวัด | |
| Dates | column | Yes | คอลัมน์วันที่จากตารางวันที่/ปฏิทิน ต้องเป็น Date/DateTime column ที่ถูกทำเครื่องหมายเป็น Date Table ในโมเดล ไม่ใช่เพียงคอลัมน์วันที่ธรรมดา | |
| Filter | filter | Optional | ตัวกรอง Boolean หรือตารางที่ใช้กรองเพิ่มเติมก่อนประเมิน Expression เช่น Product[Category] = “A” หรือ FILTER(…) | |
| YearEndDate | text | Optional | สตริงข้อความระบุวันสิ้นปีบัญชี ในรูปแบบ “MM-DD” เช่น “12-31” (ค่าเริ่มต้น) หรือ “03-31” สำหรับปีบัญชีสิ้นสุด 31 มีนาคม |
How it works
ยอดคงเหลือปลายปี
เช่น ยอดเงินคงเหลือ ณ สิ้นปี
ปิดปีบัญชีตามวันสิ้นปีบัญชี
กำหนด YearEndDate ให้สอดคล้องกับปีบัญชีขององค์กร
Examples
ตัวอย่างที่ 1: ยอดคงเหลือสิ้นปีธรรมชาติ
Year End Balance = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date] )
Year End Balance =
CLOSINGBALANCEYEAR(
[Balance],
'Calendar'[Date]
)
ส่งคืนค่า [Balance] ณ วันที่ 31 ธันวาคมของปีในบริบทปัจจุบัน
ตัวอย่างที่ 2: ปีบัญชีที่สิ้นสุด 30 มิถุนายน
Fiscal Year End Balance = VAR FiscalYearEnd = "06-30" RETURN CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date], TRUE(), FiscalYearEnd )
Fiscal Year End Balance =
VAR FiscalYearEnd = "06-30"
RETURN
CLOSINGBALANCEYEAR(
[Balance],
'Calendar'[Date],
TRUE(),
FiscalYearEnd
)
ส่งคืนค่า [Balance] ณ วันที่ 30 มิถุนายนของปีบัญชี
ตัวอย่างที่ 3: ยอดคงเหลือสิ้นปี ตามหมวดสินค้า
Balance Year End by Category = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date], Product[Category] = "Electronics" )
Balance Year End by Category =
CLOSINGBALANCEYEAR(
[Balance],
'Calendar'[Date],
Product[Category] = "Electronics"
)
ส่งคืนค่า [Balance] ณ วันสิ้นปี สำหรับเฉพาะสินค้า Category "Electronics"
ตัวอย่างที่ 4: เปรียบเทียบยอดต้นปี กับ สิ้นปี
Year Over Year Balance Change = VAR EndYearBalance = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date] ) VAR BeginYearBalance = OPENINGBALANCEYEAR( [Balance], 'Ca…
Year Over Year Balance Change =
VAR EndYearBalance = CLOSINGBALANCEYEAR(
[Balance],
'Calendar'[Date]
)
VAR BeginYearBalance = OPENINGBALANCEYEAR(
[Balance],
'Calendar'[Date]
)
RETURN
EndYearBalance - BeginYearBalance
ส่งคืนผลต่างระหว่างยอดต้นปีและยอดสิ้นปี
FAQs
CLOSINGBALANCEYEAR ต่างจาก ENDOFYEAR อย่างไร?
ENDOFYEAR ส่งคืน “ชุดวันที่” (ตาราง) ของวันสิ้นปี ส่วน CLOSINGBALANCEYEAR ประเมิน Expression ณ วันสิ้นปีโดยตรง แล้วส่งคืน “ค่าสเกลาร์” (เลขตัวเดียว) หากอยากได้วันที่จริงของวันสิ้นปี ให้ใช้ ENDOFYEAR ถ้าอยากได้ค่า Balance ที่จุดเวลานั้น ให้ใช้ CLOSINGBALANCEYEAR
ตารางวันที่ต้องมีความต่อเนื่องหรือไม่?
ต้องอย่างแน่นอน ตารางวันที่ต้องมี “วันที่ต่อเนื่องทั้งหมด” ตั้งแต่ 1 มกราคม ถึง 31 ธันวาคม (หรือสิ้นปีบัญชี) ของแต่ละปี ถ้าหายวันไป CLOSINGBALANCEYEAR อาจจะหาวันสิ้นปีไม่เจอ หรือให้ผลลัพธ์ที่ผิด คุณต้องทำเครื่องหมายตารางเป็น Date Table ด้วย
เมื่อไหร่ใช้ CLOSINGBALANCEYEAR แทน SUM ธรรมดา?
ใช้ CLOSINGBALANCEYEAR เมื่อมาตรวัดเป็น “Semi-additive” ที่ต้องการ Snapshot ขณะเวลา ไม่ใช่ผลรวม เช่น Balance (ยอดคงเหลือ), Cash (เงินสด), Stock (คงเหลือสินค้า) ส่วน SUM ธรรมดาใช้สำหรับ “Additive” มาตรวัด เช่น Sales (ยอดขาย), Revenue (รายรับ) ที่คุณต้องการรวมตลอดปี
Context Transition คืออะไร? ทำไมสำคัญ?
Context Transition คือการเปลี่ยนจาก Row Context (แต่ละแถวในตาราง) เป็น Filter Context (ชุดตัวกรองทั้งหมด) ฟังก์ชันแบบ Time Intelligence เช่น CLOSINGBALANCEYEAR ทำสิ่งนี้อัตโนมัติ เพราะต้องหาวันสิ้นปี ซึ่งเป็นงาน Filter Context ถ้าเข้าใจสิ่งนี้ คุณจะเข้าใจว่าทำไม CLOSINGBALANCEYEAR ทำงานกับ Row Context (ในเมeasures ในการคำนวณ Measure ใช้ Iterator เช่น SUMX) ได้
ใช้ CLOSINGBALANCEYEAR ได้หลายปีพร้อมกันไหม?
ไม่ CLOSINGBALANCEYEAR ทำงานบนบริบทวันที่ปัจจุบัน (current context) เท่านั้น หากต้องการเทียบหลายปี ต้องใช้ CALCULATE เพิ่มเติมเพื่อเปลี่ยน Filter Context ตัวอย่างเช่น CALCULATE(CLOSINGBALANCEYEAR(…), YEAR(‘Calendar'[Date]) = 2024) หรือลูป SUMX ผ่านหลายปี
DirectQuery รองรับ CLOSINGBALANCEYEAR หรือไม่?
ไม่ CLOSINGBALANCEYEAR ไม่รองรับ DirectQuery Mode สำหรับ Calculated Columns หรือ Row-Level Security (RLS) เหมาะสำหรับ Import Mode เท่านั้น ถ้าใช้ DirectQuery ลองพิจารณา OPENINGBALANCEQUARTER หรือ Stored Procedure แทน
Resources & Related
Resources
Related functions
Additional Notes
CLOSINGBALANCEYEAR คือฟังก์ชัน Time Intelligence ที่อยู่ในกลุ่ม “Context Transition” ซึ่งหมายความว่ามันจะเปลี่ยนบริบทแถว (row context) เป็นบริบทตัวกรอง (filter context) อัตโนมัติ มันใช้ ENDOFYEAR ในภายใน เพื่อหาวันสิ้นปี แล้วประเมิน Expression ที่จุดวันนั้น
ที่เจ๋งของ CLOSINGBALANCEYEAR คือมันเหมาะกับมาตรวัด Semi-additive ที่ต้องการ Snapshot ขณะเวลาระบุ ไม่ใช่การรวมสะสม เช่น ยอดคงเหลือสินค้า ยอดเงินสด ยอดบัญชีเรียกเก็บเงิน หากดูตาราง Sales แล้วต้องการค่า Balance ของแต่ละเดือนสิ้นปี คุณใช้อันนี้ มันจะหาวันสิ้นปีของปีนั้นโดยอัตโนมัติ
ส่วนตัวผมมักใช้ CLOSINGBALANCEYEAR ในรายงาน Balance Sheet เพราะค่าปลายปีนี้สำคัญมาก ต้องคำนวณถูก ในขณะที่ Income Statement ใช้ SUM ปกติ เพราะเป็นอาการสะสมตลอดปี ครับ