Thep Excel
OPENINGBALANCEYEARค่า ณ จุดเปิดปี (Opening Balance)
OPENINGBALANCEYEAR คำนวณค่า Expression ณ วันแรกของปี โดยดึงค่าจากวันสิ้นปีปีก่อนหน้า เหมาะสำหรับการหา opening balance ของคงเหลือ บัญชี สต็อก และค่าที่วัดได้แค่ที่จุดเวลาเดียว
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| Expression | expression | นิพจน์หรือ Measure ที่ต้องการประเมิน ณ วันแรกของปี ส่วนใหญ่เป็น Measure ประเภท Balance (คงเหลือ) เช่น Inventory, Accounts Receivable, Cash Balance |
| Dates | column (Date) | คอลัมน์วันที่จากตารางวันที่ (Date Table) ที่มีการทำเครื่องหมายว่าเป็น Date Table และมีวันที่ต่อเนื่อง ทั้งปีจนครบ |
Additional Notes
OPENINGBALANCEYEAR เป็น Time Intelligence Function ที่ประเมิน Expression ณ วันแรกของปีภายใต้ตัวกรองปัจจุบัน โดยจะสำเร็จเสมือนประเมินค่า “ณ วันสิ้นปีปีก่อนหน้า” ซึ่งเทียบเท่ากับ CALCULATE(Expression, PREVIOUSDAY(STARTOFYEAR(Dates)))
ที่เจ๋งคือ ฟังก์ชันนี้คำนึงถึง “semi-additive” measures แบบ Balance, Inventory, Quantity on Hand ที่หนึ่ง ในขณะที่ Sales หรือ Revenue นั้นสรุป/รวมได้ Balance ไม่ได้ – มันเป็นค่า ณ จุดเวลา พอมี OPENINGBALANCEYEAR ก็หา “ค่าเปิดปี” เพื่อเทียบกับ “ค่าปิดปี” หรือวิเคราะห์การเปลี่ยนแปลง
ส่วนตัวผมคิดว่า Time Intelligence Functions (OPENINGBALANCEYEAR, CLOSINGBALANCEYEAR, CLOSINGBALANCEMONTH) เป็น “hidden gems” ที่จริงจริง มันช่วยการวิเคราะห์ข้อมูลทางการเงินและสินค้าคงคลัง จริงๆ ที่เข้าใจจังหวะเวลา แล้วคุณเข้าใจ DAX ระดับหนึ่งแล้ว
How it works
ยอดคงเหลือเปิดปี
ใช้ทำรายงานเปลี่ยนแปลงยอดคงเหลือรายปี
เทียบเปิดปีและ YTD
เทียบ opening balance กับตัวชี้วัดสะสมระหว่างปี
Examples
ตัวอย่างที่ 1: หา Opening Balance ของสินค้าคงคลังInventory Opening = OPENINGBALANCEYEAR( [Inventory Balance], 'Date'[Date] )
เหมาะกับ semi-additive measure ประเภท stock/balance ที่ต้องหาค่าเปิดงวด เพื่อเทียบกับค่าปิดปี หรือวิเคราะห์การเปลี่ยนแปลงสินค้าในช่วงปี
ตัวอย่างที่ 2: คำนวณการเปลี่ยนแปลงยอดเงินในบัญชี (Opening → Closing)Net Change (Year) = VAR OpenBal = OPENINGBALANCEYEAR( [Account Balance], 'Date'[Date] ) VAR CloseBal = CLOSINGBALANCEYEAR( [Account Balance], 'Date'[Date] ) RET…
ฟังก์ชันเหมาะกับรายงานการวิเคราะห์การเปลี่ยนแปลง (Change Analysis) ในสินค้า บัญชี หรือเงินสดตลอดปี โดยใช้ VAR เก็บค่าเปิด-ปิดก่อน แล้วค่อยลบออก จะ debug ง่ายกว่า nested CALCULATE
ตัวอย่างที่ 3: หา Inventory Growth จากเปิดปีYTD Inventory Growth = VAR CurrentBal = [Inventory Balance] VAR OpeningBal = OPENINGBALANCEYEAR( [Inventory Balance], 'Date'[Date] ) RETURN IF( OpeningBal = 0,…
ใช้ IF + DIVIDE เพื่อหลีกเลี่ยงการหารด้วยศูนย์ เหมาะกับการวิเคราะห์ทาง % Growth ของสินค้าคงคลัง หรือการเติบโตจากต้นปีจนถึงปัจจุบัน (YTD Growth)
ตัวอย่างที่ 4: เทียบ Opening Balance ปีต่างๆ (Multi-Year)Comparison: Opening This Year vs Last Year = VAR ThisYearOpening = OPENINGBALANCEYEAR( [Balance], 'Date'[Date] ) VAR LastYearOpening = OPENINGBALANCEYEAR( [Bala…
ผสม OPENINGBALANCEYEAR + DATEADD เพื่อเทียบ opening balance ของปีต่างๆ เหมาะกับ Year-over-Year (YoY) Analysis ในภาพของ opening position เพื่อเข้าใจการเปลี่ยนแปลงโครงสร้างจากต้นปี
FAQs
OPENINGBALANCEYEAR ประเมินจากวันไหนจริงๆ?+
OPENINGBALANCEYEAR ประเมินจากวันแรก (First Day) ของปี โดยดึงค่าจากวันสิ้นปีปีก่อนหน้า (Last Day of Previous Year) เสมือน CALCULATE(Expression, PREVIOUSDAY(STARTOFYEAR(Dates))) ดังนั้น ถ้าปีเริ่ม 1 มกราคม ฟังก์ชันจะหาค่า ณ 31 ธันวาคม ปีก่อน
ต่างอย่างไร OPENINGBALANCEYEAR vs PREVIOUSYEAR?+
PREVIOUSYEAR คืนตารางวันที่ (ทั้งปีที่แล้ว) เพื่อใช้เป็นตัวกรองสำหรับ CALCULATE ส่วน OPENINGBALANCEYEAR คืนค่า scalar (เลขตัวเดียว) ของ Expression ณ จุดเปิดปี เหตุผลง่ายๆ PREVIOUSYEAR = table, OPENINGBALANCEYEAR = value
ฟังก์ชันนี้เหมาะกับ measure ประเภทไหน?+
เหมาะกับ semi-additive measures ที่เป็น Balance, Inventory, Quantity, Accounts Receivable ที่วัดได้แค่ณ จุดเวลาเดียว (point-in-time snapshot) แต่ไม่เหมาะกับ Sales, Revenue, Cost ที่เป็นยอดรวมของช่วง เพราะวัดค่าได้ตลอดช่วงเวลา
จะรู้ได้ไง measure ที่ใช้ OPENINGBALANCEYEAR นั้นถูกต้อง?+
ลองหา Opening + Closing Balance แล้วลบออก ผลที่ได้ควรตรงกับการเปลี่ยนแปลงเบื้องต้น (First Principles Check) เช่น Opening Inventory 100 + Purchases 50 – Sales 30 = Closing Inventory 120 ถ้าตรง แสดงว่า measure ของคุณ semi-additive และ OPENINGBALANCEYEAR ใช้ได้
ถ้าเปลี่ยนปีงบประมาณ OPENINGBALANCEYEAR จะยังใช้ได้ไหม?+
ยังใช้ได้ แต่ต้องให้ Date Table และ Data Model สอดคล้องกับปีงบประมาณ ถ้าปีงบประมาณเริ่มเดือน เมษายน ให้ตรวจว่า Dates column ในฟังก์ชันสาย "ปีพิษฐ์" อย่างถูกต้อง บ้างก็เพิ่มคอลัมน์ Fiscal Year แล้วใช้ logic สอดคล้อง
OPENINGBALANCEYEAR ทำ context transition ไหม?+
ใช่ ทำ context transition จาก row context → filter context เหมือน CALCULATE ฉะนั้น ถ้าใช้ใน row context (เช่น calculated column) มันจะแปลง context อย่างชาญฉลาด ระวังบ้างว่า filter context ที่เกิดขึ้นจริงๆ มันเป็นแบบไหน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ OPENINGBALANCEYEAR จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CLOSINGBALANCEMONTH ประเมิน Expression ณ วันสิ้นเดือนของบริบทวันที่ปัจจุบัน เหมาะกับการทำมาตรวัดยอดคงเหลือปลายเดือน และรองรับการใส่ตัวกรองเพิ่มเติม
CLOSINGBALANCEQUARTER ประเมิน Expression ณ วันสิ้นไตรมาสของบริบทวันที่ปัจจุบัน เหมาะกับมาตรวัดยอดคงเหลือปลายไตรมาส และรองรับตัวกรองเพิ่มเติม
ฟังก์ชันเวลา Intelligence ที่ประเมิน Expression ณ วันสิ้นปีของปีในบริบทปัจจุบัน รองรับปีบัญชี (Fiscal Year) และตัวกรองเพิ่มเติม เหมาะสำหรับมาตรวัด Semi-additive เช่น ยอดคงเหลือ ยอดเงินสด
DATEADD คืนค่าตารางวันที่ ที่ถูกเลื่อนออกไปจากช่วงวันที่ใน Context ปัจจุบัน ตามจำนวน (NumberOfIntervals) และหน่วย (Interval) ที่กำหนด เช่น เลื่อนย้อนหลัง 1 เดือน หรือเลื่อนไปข้างหน้า 1 ปี
DATESYTD คืนตารางวันที่ของช่วงตั้งแต่ต้นปีถึงวันที่สุดท้ายที่มองเห็นใน Filter Context ใช้เป็นตัวกรองเวลาใน CALCULATE สำหรับการคำนวณยอดสะสมตั้งแต่ต้นปี รองรับทั้งปีปฏิทินและปีบัญชี (Fiscal Year) ด้วย YearEndDate argument
LASTDATE คืนค่าตารางที่มีวันที่สุดท้ายในบริบทตัวกรองปัจจุบัน มักใช้เป็นตารางตัวกรองให้ CALCULATE เพื่อคำนวณ measure ณ วันสุดท้ายของช่วงที่เลือก และสามารถใช้ MAXX เพื่อแปลงผลเป็น scalar date ได้
OPENINGBALANCEMONTH ประเมิน Expression ณ จุดเปิดเดือน (วันสิ้นเดือนก่อนหน้า) เหมาะสำหรับหา opening balance ของยอดคงเหลือ สต็อก หรือสถิติ ณ จุดเริ่มเดือน
OPENINGBALANCEQUARTER ประเมิน Expression ณ วันที่เทียบเท่ากับวันสิ้นไตรมาสก่อนหน้า ภายใต้บริบทตัวกรองที่กำหนด ใช้เพื่อหา “ค่าเปิดงวดรายไตรมาส”
OPENINGBALANCEWEEK ประเมิน Expression ณ จุดเปิดสัปดาห์ (คือวันสิ้นสัปดาห์ก่อนหน้า) ใช้เพื่อเปรียบเทียบค่าเปลี่ยนแปลงระหว่างเปิดและปิดสัปดาห์
PREVIOUSDAY คืนตารางวันที่ของวันก่อนหน้า ตามบริบทวันที่ปัจจุบัน เหมาะสำหรับทำตัวชี้วัดแบบเทียบวันต่อวัน โดยนำตารางวันที่ไปใช้เป็นตัวกรองเวลา
PREVIOUSYEAR คืนตารางวันที่ของปีก่อนหน้า ตามบริบทวันที่ปัจจุบัน เหมาะสำหรับตัวชี้วัดแบบ Year-over-Year โดยนำตารางวันที่ไปใช้เป็นตัวกรองเวลา
STARTOFYEAR คืนตาราง 1 แถวที่มีวันที่วันแรกของปีในบริบทปัจจุบัน ใช้สำหรับ semi-additive measure เช่น inventory balance หรือเปรียบเทียบ year-to-date
TOTALYTD คำนวณยอดสะสมตั้งแต่ต้นปี (Year-to-Date) ของ expression ใดๆ โดยอิงจาก Date table และสามารถกำหนด fiscal year end date เพื่อรองรับปีงบประมาณที่ไม่ตรงกับปีปฏิทินได้
.
ส่วนตัวผมใช้บ่อยมากตอนต้องวิเคราะห์ยอดขาย KPI และรายงานทางการเงินที่ต้องการติดตามความก้าวหน้าตลอดทั้งปีครับ
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
อีเมลของคุณจะไม่ถูกเผยแพร่