Thep Excel
OPENINGBALANCEMONTHคำนวณค่า ณ วันเปิดเดือน
OPENINGBALANCEMONTH ประเมิน Expression ณ จุดเปิดเดือน (วันสิ้นเดือนก่อนหน้า) เหมาะสำหรับหา opening balance ของยอดคงเหลือ สต็อก หรือสถิติ ณ จุดเริ่มเดือน
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| Expression | expression | นิพจน์หรือ measure ที่ต้องการประเมิน ณ จุดเปิดเดือน โดยมักจะเป็น measure ที่หา balance/ยอดคงเหลือ |
| Dates | column (date/time) | คอลัมน์วันที่จากตารางวันที่ (Date table) ที่ใช้เป็นตัวอ้างอิง หรือ table expression ที่ให้คอลัมน์วันที่ |
Additional Notes
OPENINGBALANCEMONTH เป็นฟังก์ชัน Time Intelligence ของ DAX ที่ทำหน้าที่ประเมิน Expression ณ “วันที่เทียบเท่ากับวันสิ้นเดือนก่อนหน้า” ตามบริบท Filter Context ที่กำหนด มันอยู่ในกลุ่มฟังก์ชันสำหรับจัดการ opening/closing balance ที่มักใช้กับ measure แบบ semi-additive (ยอดคงเหลือ ไม่ใช่ยอดสะสม)
ที่เจ๋งคือ OPENINGBALANCEMONTH ประหยัดเวลาเมื่อต้องหา “ค่าเปิดงวด” โดยไม่ต้องเขียน CALCULATE + PREVIOUSDAY + STARTOFMONTH เอง มันทำงานดี ๆ กับ measure เชิงสต็อก เช่น inventory balance, bank account balance, หรือจำนวนพนักงาน ณ วันเฉพาะ ซึ่งต้องใช้ “ค่าสุดท้ายของช่วงเวลา” มากกว่าการ summarize ทั้งช่วง
ส่วนตัวผมมักใช้ OPENINGBALANCEMONTH คู่กับ CLOSINGBALANCEMONTH เพื่อวิเคราะห์การเปลี่ยนแปลง (movement) ของยอดคงเหลือในแต่ละเดือน แล้วนำไปใช้ในรายงาน KPI หรือ Dashboard เพื่อให้เห็นภาพรวมของการเปลี่ยนแปลง ครับ
How it works
ยอดคงเหลือเปิดเดือน
หายอดคงเหลือ ณ จุดเริ่มเดือน เพื่อเทียบกับค่าปิดเดือน
สต็อกเปิดเดือน/จำนวนคงค้าง
ใช้กับ measure คงค้างที่ต้องการอ่าน ณ จุดเวลา
Examples
ตัวอย่างที่ 1: Opening Balance ของยอดสต็อกOpening Inventory Balance = OPENINGBALANCEMONTH( [Current Inventory Value], 'Date'[Date] )
สมมุติว่า [Current Inventory Value] เป็น measure หาค่าเก็บสต็อกปัจจุบัน ตอนที่ filter โดย 'Date'[Date] = 2024-02-15 มันจะส่งกลับค่ายอดสต็อก ณ 2024-01-31 (วันสิ้นเดือนมกราคม) ซึ่งคือ opening balance ของเดือนกุมภาพันธ์
ตัวอย่างที่ 2: เปรียบเทียบเปิด-ปิด (Movement Analysis)Monthly Movement = [Closing Month Balance] – [Opening Month Balance] // หรือในรูปแบบ VAR Monthly Movement (VAR) = VAR OpenBalance = OPENINGBALANCEMONTH([Balance…
ใช้เปรียบเทียบระหว่าง [Opening Month Balance] (OPENINGBALANCEMONTH) และ [Closing Month Balance] (CLOSINGBALANCEMONTH) เพื่อดู movement โดยค่า positive หมายถึง "เพิ่มขึ้น" ค่า negative หมายถึง "ลดลง" เหมาะสำหรับ KPI Dashboard ที่ต้องแสดงการเปลี่ยนแปลง
ตัวอย่างที่ 3: CALCULATE + Filter เพิ่มเติมOpening Balance (ประเภท A เท่านั้น) = CALCULATE( OPENINGBALANCEMONTH( [Total Balance], 'Date'[Date] ), Product[Category] = "A" )
OPENINGBALANCEMONTH นั้นเคารพ filter context จาก CALCULATE ดังนั้นโดยการใส่ boolean filter Product[Category] = "A" มันจะประเมิน [Total Balance] ณ วันเปิดเดือน เฉพาะขอบเขตประเภท A เท่านั้น
FAQs
OPENINGBALANCEMONTH กับ PREVIOUSMONTH ต่างกันอย่างไร?+
PREVIOUSMONTH คืนค่าตารางวันที่ของเดือนก่อนหน้า เพื่อใช้กรองข้อมูล ส่วน OPENINGBALANCEMONTH คือการประเมิน Expression เอง ณ จุดเวลา "วันสิ้นเดือนก่อนหน้า" (ซึ่งคือ opening ของเดือนปัจจุบัน) โดยคืนค่าเดี่ยว (scalar) ไม่ใช่ตาราง หากต้องการใช้ PREVIOUSMONTH ให้ filter ต้องเขียน VAR PrevMonth = PREVIOUSMONTH('Date'[Date]) แล้วใช้กับ CALCULATE
ทำไมถึงบอกว่าคืนค่า ณ วันสิ้นเดือนก่อนหน้า ไม่ใช่วันเปิดเดือนปัจจุบัน?+
เพราะจากมุมมองการบัญชี/ทางการเงิน วันสิ้นเดือนเดือนที่ 1 (2024-01-31) = วันเปิดเดือนที่ 2 (2024-02-01) ซึ่งคือเวลาเดียวกัน ดังนั้นค่าคงเหลือ ณ จุดเวลานี้คือ "opening balance" ของเดือนที่ 2 และ "closing balance" ของเดือนที่ 1 พูดแบบเรียบง่าย OPENINGBALANCEMONTH ไปดึงค่า "สิ้นเดือนก่อนหน้า" ซึ่งก็คือ opening ของเดือนปัจจุบัน ครับ
OPENINGBALANCEMONTH ใช้ได้กับ DirectQuery ไหม?+
ไม่ได้ครับ OPENINGBALANCEMONTH เป็น Time Intelligence function ที่ต้องใช้กับ Import Mode เท่านั้น ถ้าลองใช้กับ DirectQuery model จะ error ตัว function นี้คำนวณ context transition อยู่ในหน่วยความจำจาก Power BI/Excel ไม่ได้ translate เป็น SQL/DAX ที่ backend รองรับ
ต้องกำหนด date table ไหม? หรือใช้คอลัมน์วันที่ตามปกติได้?+
ดีที่สุดคือ ให้มี date table ที่ mark เป็น "Date Table" ใน Power BI model ถึงแม้ว่า OPENINGBALANCEMONTH จะใช้คอลัมน์ date column ตามธรรมชาติได้ แต่เมื่อ mark ให้เป็น date table อย่างเป็นทางการ Power BI จะประมวลผลความสัมพันธ์และ filter context ได้ดีขึ้น นอกจากนั้นก็ยังช่วยให้ฟังก์ชัน Time Intelligence อื่น ๆ เช่น YTD, MTD ทำงานได้อย่างถูกต้อง
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ OPENINGBALANCEMONTH จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CLOSINGBALANCEMONTH ประเมิน Expression ณ วันสิ้นเดือนของบริบทวันที่ปัจจุบัน เหมาะกับการทำมาตรวัดยอดคงเหลือปลายเดือน และรองรับการใส่ตัวกรองเพิ่มเติม
DATESMTD คืนตารางวันที่ของช่วงตั้งแต่ต้นเดือนถึงวันที่ล่าสุดใน filter context เหมาะกับการทำยอดสะสมตั้งแต่ต้นเดือน (MTD) โดยใช้ร่วมกับ CALCULATE และควรใช้คู่กับ ShowValueForDates pattern เพื่อไม่ให้แสดงค่าในวันที่ยังไม่มีข้อมูล
ENDOFMONTH คืนตารางวันที่ของวันสิ้นเดือนตามปฏิทิน เหมาะกับ Semi-Additive Calculations เช่น ยอดคงเหลือ, Inventory, หรือ Closing Balance ที่ต้องการ snapshot ณ สิ้นเดือน ไม่ว่าจะมีข้อมูลจริงในวันนั้นหรือไม่
LASTDATE คืนค่าตารางที่มีวันที่สุดท้ายในบริบทตัวกรองปัจจุบัน มักใช้เป็นตารางตัวกรองให้ CALCULATE เพื่อคำนวณ measure ณ วันสุดท้ายของช่วงที่เลือก และสามารถใช้ MAXX เพื่อแปลงผลเป็น scalar date ได้
OPENINGBALANCEQUARTER ประเมิน Expression ณ วันที่เทียบเท่ากับวันสิ้นไตรมาสก่อนหน้า ภายใต้บริบทตัวกรองที่กำหนด ใช้เพื่อหา “ค่าเปิดงวดรายไตรมาส”
OPENINGBALANCEWEEK ประเมิน Expression ณ จุดเปิดสัปดาห์ (คือวันสิ้นสัปดาห์ก่อนหน้า) ใช้เพื่อเปรียบเทียบค่าเปลี่ยนแปลงระหว่างเปิดและปิดสัปดาห์
OPENINGBALANCEYEAR คำนวณค่า Expression ณ วันแรกของปี โดยดึงค่าจากวันสิ้นปีปีก่อนหน้า เหมาะสำหรับการหา opening balance ของคงเหลือ บัญชี สต็อก และค่าที่วัดได้แค่ที่จุดเวลาเดียว
PREVIOUSMONTH คืนตารางวันที่ทั้งหมดของเดือนก่อนหน้า โดยอ้างอิงจากวันแรกของบริบทปัจจุบัน เหมาะกับการทำ Measure เปรียบเทียบ Month-over-Month เช่น ยอดขายเดือนก่อนหน้า ข้อแตกต่างสำคัญกับ DATEADD คือ PREVIOUSMONTH คืนทั้งเดือนเสมอ ไม่ว่าจะเลือกวันไหนก็ตาม
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
STARTOFMONTH คืนวันแรกของเดือนเป็นตาราง 1 แถว ใช้กับการวัดที่ขึ้นต่อจุดเวลา เช่น ยอดคงเหลือ บัญชี หรือสินค้าคงคลัง
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
อีเมลของคุณจะไม่ถูกเผยแพร่