Thep Excel
CLOSINGBALANCEYEARยอดคงเหลือสิ้นปี
ฟังก์ชันเวลา Intelligence ที่ประเมิน Expression ณ วันสิ้นปีของปีในบริบทปัจจุบัน รองรับปีบัญชี (Fiscal Year) และตัวกรองเพิ่มเติม เหมาะสำหรับมาตรวัด Semi-additive เช่น ยอดคงเหลือ ยอดเงินสด
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| Expression | scalar | นิพจน์หรือมาตรวัดที่ต้องการประเมิน เช่น [Balance], [Cash], SUMX(…) มันสามารถเป็นฟังก์ชันหรือมาตรวัด |
| Dates | column | คอลัมน์วันที่จากตารางวันที่/ปฏิทิน ต้องเป็น Date/DateTime column ที่ถูกทำเครื่องหมายเป็น Date Table ในโมเดล ไม่ใช่เพียงคอลัมน์วันที่ธรรมดา |
| [Filter]ไม่บังคับ | filter | ตัวกรอง Boolean หรือตารางที่ใช้กรองเพิ่มเติมก่อนประเมิน Expression เช่น Product[Category] = “A” หรือ FILTER(…) |
| [YearEndDate]ไม่บังคับ | text | สตริงข้อความระบุวันสิ้นปีบัญชี ในรูปแบบ “MM-DD” เช่น “12-31” (ค่าเริ่มต้น) หรือ “03-31” สำหรับปีบัญชีสิ้นสุด 31 มีนาคม |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
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 ปกติ เพราะเป็นอาการสะสมตลอดปี ครับ
How it works
ยอดคงเหลือปลายปี
เช่น ยอดเงินคงเหลือ ณ สิ้นปี
ปิดปีบัญชีตามวันสิ้นปีบัญชี
กำหนด YearEndDate ให้สอดคล้องกับปีบัญชีขององค์กร
Examples
ตัวอย่างที่ 1: ยอดคงเหลือสิ้นปีธรรมชาติYear End Balance = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date] )
หากบริบทคือ 2024 มันจะหาวันที่ 31 ธันวาคม 2024 แล้วประเมิน [Balance] ณ วันนั้น เหมาะกับรายงานปลายปี
ตัวอย่างที่ 2: ปีบัญชีที่สิ้นสุด 30 มิถุนายนFiscal Year End Balance = VAR FiscalYearEnd = "06-30" RETURN CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date], TRUE(), FiscalYearEnd )
ถ้าบริษัทของคุณใช้ปีบัญชีสิ้นสุด 30 มิถุนายน ใช้พารามิเตอร์ YearEndDate เพื่อบอกว่าวันสิ้นปี คือ 06-30 ส่วน TRUE() คือตัวกรองว่างเพื่อเว้นพารามิเตอร์ Filter
ตัวอย่างที่ 3: ยอดคงเหลือสิ้นปี ตามหมวดสินค้าBalance Year End by Category = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date], Product[Category] = "Electronics" )
เพิ่มตัวกรองในพารามิเตอร์ที่สามเพื่อจำกัด Expression ให้ประเมินเฉพาะแถวที่ตรงกับเงื่อนไข ช่วยเมื่อต้องการยอดคงเหลือสิ้นปีแยกตามหมวดหมู่
ตัวอย่างที่ 4: เปรียบเทียบยอดต้นปี กับ สิ้นปีYear Over Year Balance Change = VAR EndYearBalance = CLOSINGBALANCEYEAR( [Balance], 'Calendar'[Date] ) VAR BeginYearBalance = OPENINGBALANCEYEAR( [Balance], 'Ca…
ใช้ VAR เก็บผลลัพธ์จาก CLOSINGBALANCEYEAR และ OPENINGBALANCEYEAR แล้วค่อยหาผลต่าง เหมาะสำหรับดูว่ายอดคงเหลือเปลี่ยนแปลงไปเท่าไรตลอดปี
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ CLOSINGBALANCEYEAR จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CLOSINGBALANCEMONTH ประเมิน Expression ณ วันสิ้นเดือนของบริบทวันที่ปัจจุบัน เหมาะกับการทำมาตรวัดยอดคงเหลือปลายเดือน และรองรับการใส่ตัวกรองเพิ่มเติม
CLOSINGBALANCEQUARTER ประเมิน Expression ณ วันสิ้นไตรมาสของบริบทวันที่ปัจจุบัน เหมาะกับมาตรวัดยอดคงเหลือปลายไตรมาส และรองรับตัวกรองเพิ่มเติม
CLOSINGBALANCEWEEK ประเมินนิพจน์ (มาตรวัด) ที่วันสิ้นสัปดาห์ของบริบทวันที่ปัจจุบัน สำคัญสำหรับรายงานปิดสัปดาห์และ snapshot ยอดคงเหลือ
ENDOFYEAR คืนตารางวันที่ของวันสิ้นปีตามบริบทวันที่ปัจจุบัน และรองรับการกำหนดวันสิ้นปีบัญชี เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อประเมินมาตรวัดที่วันสิ้นปี
OPENINGBALANCEYEAR คำนวณค่า Expression ณ วันแรกของปี โดยดึงค่าจากวันสิ้นปีปีก่อนหน้า เหมาะสำหรับการหา opening balance ของคงเหลือ บัญชี สต็อก และค่าที่วัดได้แค่ที่จุดเวลาเดียว
PREVIOUSYEAR คืนตารางวันที่ของปีก่อนหน้า ตามบริบทวันที่ปัจจุบัน เหมาะสำหรับตัวชี้วัดแบบ Year-over-Year โดยนำตารางวันที่ไปใช้เป็นตัวกรองเวลา
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
SUMX เป็น iterator function ที่ทรงพลังใน DAX ออกแบบมาเพื่อวนลูปทีละแถวในตารางที่กำหนด แล้วคำนวณ expression ที่ซับซ้อนสำหรับแต่ละแถวก่อนนำผลลัพธ์ทั้งหมดมารวมกัน ต่างจาก SUM ที่รวมเฉพาะค่าใน column เดียว SUMX สร้าง row context สำหรับแต่ละแถว ทำให้สามารถคำนวณ expression อย่าง Quantity × Price หรือใช้ RELATED ดึงข้อมูลข้ามตารางได้ มีกลไก context transition อัตโนมัติเมื่อเจอ measure ในนั้น เหมาะสำหรับการคำนวณซับซ้อนระดับแถวข้อมูล ประหยัดพื้นที่ model เพราะไม่ต้องสร้าง calculated column แต่ช้ากว่า SUM และไม่รองรับ DirectQuery mode ใน calculated columns หรือ RLS rules
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่