Thep Excel
TOTALYTDยอดสะสมตั้งแต่ต้นปี (Year-to-Date)
TOTALYTD คำนวณยอดสะสมตั้งแต่ต้นปี (Year-to-Date) ของ expression ใดๆ โดยอิงจาก Date table และสามารถกำหนด fiscal year end date เพื่อรองรับปีงบประมาณที่ไม่ตรงกับปีปฏิทินได้
.
ส่วนตัวผมใช้บ่อยมากตอนต้องวิเคราะห์ยอดขาย KPI และรายงานทางการเงินที่ต้องการติดตามความก้าวหน้าตลอดทั้งปีครับ
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| Expression | expression | Expression ที่ต้องการคำนวณยอดสะสม YTD เช่น SUM(Sales[Amount]) หรือ [Total Sales] สามารถเป็น measure reference หรือ aggregation expression ใดๆ ที่ return scalar value | |
| Dates | column | Column ที่เก็บวันที่จาก Date table ที่มีวันที่ต่อเนื่องครบถ้วนตลอดทั้งปี เช่น ‘Date'[Date] หรือ DateTime[DateKey] ต้อง mark as date table ในโมเดลเพื่อให้ time intelligence ทำงานถูกต้อง | |
| [Filter]ไม่บังคับ | expression | ไม่มีการกรองเพิ่มเติม | Boolean expression หรือ table expression ที่ใช้กรองข้อมูลเพิ่มเติมก่อนคำนวณ YTD เช่น Products[Category] = “Electronics” ทำงานภายใน CALCULATE context ตาม CALCULATE function rules |
| [YearEndDate]ไม่บังคับ | text | "12/31" (ปีปฏิทิน) | String literal ที่ระบุวันสิ้นปีงบประมาณในรูปแบบ “MM/DD” เช่น “06/30” สำหรับปีงบประมาณที่สิ้นสุด 30 มิถุนายน หรือ “03/31” สำหรับสิ้นสุด 31 มีนาคม ไม่ต้องระบุปี (ปีจะถูกละเว้น) ต้องเป็นข้อความคงที่ไม่สามารถใช้ column หรือ variable ได้ |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
TOTALYTD เป็น DAX time intelligence function ที่ออกแบบมาเพื่อคำนวณยอดสะสมตั้งแต่วันแรกของปีจนถึงวันที่ล่าสุดใน filter context ปัจจุบัน
.
ที่เจ๋งคือฟังก์ชันนี้รองรับ fiscal year (ปีงบประมาณ) ด้วย ทำให้องค์กรที่มีรอบปีงบประมาณไม่ตรงกับปีปฏิทินสามารถคำนวณยอดสะสมได้ถูกต้องตามรอบปีงบประมาณของตนเอง
.
ส่วนตัวผมใช้บ่อยมากครับ โดยเฉพาะเวลาต้องสร้าง measure ยอดขาย KPI และรายงานทางการเงินที่ต้องติดตามความก้าวหน้าตลอดทั้งปี 😎
ข้อควรระวังในการใช้งาน
- ต้องมี Date table ที่ถูกต้อง: วันที่ต้องต่อเนื่องครบทุกวันตลอดทั้งปี
- YearEndDate เป็น string literal: ต้องระบุเป็นข้อความคงที่ เช่น “06/30” ไม่สามารถใช้ column reference หรือ variable ได้
- Filter context มีความสำคัญ: ผลลัพธ์จะขึ้นอยู่กับ filter context ที่ใช้งาน
- Performance consideration: ควรใช้ Date table แทนการ filter ด้วยวันที่โดยตรงจาก transaction table
เปรียบเทียบกับ DATESYTD และ CALCULATE
TOTALYTD เป็น time intelligence function ที่ทำงานเทียบเท่ากับการเขียน CALCULATE ร่วมกับ DATESYTD
.
ที่ต้องระวังคือ DATESYTD จะ return table ของวันที่ในช่วง year-to-date ส่วน TOTALYTD จะ return ค่าผลลัพธ์ของ expression ที่คำนวณแล้ว ทำให้ใช้งานง่ายและอ่านโค้ดเข้าใจได้ทันทีครับ 💡
How it works
ยอดขาย YTD สำหรับ KPI Dashboard
สร้าง measure ยอดขายสะสมตั้งแต่ต้นปีเพื่อแสดงใน dashboard และเปรียบเทียบกับเป้าหมายรายปี ช่วยให้เห็นความก้าวหน้าของยอดขายตลอดทั้งปีได้ชัดเจน
รายงานการเงินแบบ Fiscal Year
คำนวณรายได้หรือกำไรสะสมตามรอบปีงบประมาณที่เริ่มต้นไม่ใช่วันที่ 1 มกราคม เช่น องค์กรที่ใช้ปีงบประมาณเริ่ม 1 ก.ค. – 30 มิ.ย. สามารถระบุ YearEndDate เป็น "06/30"
เปรียบเทียบยอดสะสมแต่ละหมวดหมู่
วิเคราะห์ยอดขายสะสม YTD ของแต่ละหมวดสินค้าหรือแต่ละภูมิภาค เพื่อเปรียบเทียบประสิทธิภาพการขายระหว่าง category หรือ region ต่างๆ
Cumulative Metrics สำหรับ Time Series Analysis
แสดงกราฟเส้นของยอดสะสม YTD เทียบกับเดือนต่างๆ เพื่อดู trend การเติบโตของตัวเลขสำคัญตลอดทั้งปี เช่น ยอดขาย ลูกค้าใหม่ หรือ revenue
YTD with Filtering สำหรับ Product Category Analysis
คำนวณยอดสะสม YTD เฉพาะหมวดสินค้าหรือเฉพาะช่องทางการขายบางช่องทาง โดยใช้ Filter argument เพื่อจำกัดขอบเขตข้อมูลที่ต้องการวิเคราะห์
Examples
ตัวอย่างที่ 1: ยอดขาย YTD พื้นฐาน (Foundation)Sales YTD = TOTALYTD( SUM(Sales[Amount]), 'Date'[Date] )
ตัวอย่างพื้นฐานที่สุดของการใช้ TOTALYTD โดยรวมยอดขายทั้งหมดตั้งแต่วันแรกของปี (1 มกราคม) จนถึงวันที่ปัจจุบันใน context
สมมติว่าวันนี้เป็นวันที่ 31 มีนาคม 2024 measure นี้จะรวมยอดขายตั้งแต่ 1 ม.ค. – 31 มี.ค. 2024 ส่วนตัวผมใช้แบบนี้ใน KPI card บ่อยมากครับ 😎
ตัวอย่างที่ 2: YTD แบบปีงบประมาณ (Practical – Fiscal Year)Sales YTD Fiscal = TOTALYTD( SUM(Sales[Amount]), 'Date'[Date], , "06/30" )
สำหรับองค์กรที่มีปีงบประมาณเริ่มต้น 1 กรกฎาคม และสิ้นสุด 30 มิถุนายน โดยระบุ YearEndDate เป็น "06/30" เพื่อให้ระบบรู้ว่าต้นปีคือ 1 ก.ค.
สมมติว่าวันนี้เป็น 31 ตุลาคม 2024 measure นี้จะรวมยอดขายตั้งแต่ 1 ก.ค. 2024 – 31 ต.ค. 2024 ซึ่งเป็น fiscal YTD ที่ถูกต้องตามรอบปีงบประมาณครับ 💡
ตัวอย่างที่ 3: YTD พร้อม Filter (Advanced – With Filtering)Electronics YTD = TOTALYTD( SUM(Sales[Amount]), 'Date'[Date], Products[Category] = "Electronics" )
ใช้ Filter argument เพื่อคำนวณ YTD เฉพาะหมวดสินค้า Electronics โดยกรองข้อมูลก่อนคำนวณยอดสะสม
เหมือนกับการเขียน CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date]), Products[Category] = "Electronics") สมมติว่าใน Q1 2024 มียอดขาย Electronics 3 เดือน (ม.ค. 5,000 / ก.พ. 7,000 / มี.ค. 8,000) ผลลัพธ์ ณ วันที่ 31 มี.ค. จะเป็น 20,000 ครับ
ตัวอย่างที่ 4: YTD เปรียบเทียบกับ Previous Year (Complex – Multi-Step)YTD vs PY = VAR CurrentYTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]) VAR PreviousYTD = CALCULATE( TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]), SAMEPERIODLASTY…
ใช้ VAR…RETURN pattern เพื่อสร้าง measure ที่ซับซ้อน โดยคำนวณ CurrentYTD ด้วย TOTALYTD จากนั้นคำนวณ PreviousYTD โดยใช้ CALCULATE ร่วมกับ SAMEPERIODLASTYEAR เพื่อเลื่อน context ไปปีก่อนหน้า
แล้วคำนวณ Growth และ return เป็นเปอร์เซ็นต์การเติบโต สมมติ CurrentYTD = 150,000 และ PreviousYTD = 120,000 ผลลัพธ์จะเป็น 0.25 หรือ 25% growth ส่วนตัวผมใช้ pattern นี้บ่อยมากตอน year-over-year analysis ครับ 😎
ตัวอย่างที่ 5: YTD แบบ Fiscal Year พร้อม Filter (Real-World Complex)Active Customers YTD Fiscal = VAR ActiveCustomers = DISTINCTCOUNT(Sales[CustomerID]) VAR Result = TOTALYTD( ActiveCustomers, 'Date'[Date], Customers[Status] = "…
ตัวอย่าง real-world ที่ใช้ทั้ง VAR, TOTALYTD, fiscal year และ filter ร่วมกัน เพื่อนับจำนวนลูกค้าที่ active สะสมตั้งแต่ต้นปีงบประมาณ (1 เมษายน) จนถึงปัจจุบัน
โดยกรองเฉพาะลูกค้าที่มี Status = "Active" และใช้ YearEndDate = "03/31" เพื่อให้ระบบรู้ว่าปีงบประมาณเริ่มต้น 1 เมษายนและสิ้นสุด 31 มีนาคม เหมาะสำหรับองค์กรที่ใช้ fiscal year แบบญี่ปุ่นหรืออินเดียครับ 💡
FAQs
TOTALYTD ต่างจาก DATESYTD อย่างไร?+
DATESYTD เป็น table function ที่ return ตารางของวันที่ในช่วง year-to-date (เช่น 1 ม.ค. – 31 มี.ค.) ซึ่งต้องใช้ร่วมกับ CALCULATE เพื่อคำนวณค่า expression
ส่วน TOTALYTD เป็น syntax sugar ที่รวมทั้งสองอย่างเข้าด้วยกัน คือ return ค่าผลลัพธ์ของ expression ที่คำนวณแล้วโดยตรง ส่วนตัวผมชอบใช้ TOTALYTD มากกว่าครับ เพราะอ่านง่ายและเขียนสั้นกว่า 😎
YearEndDate ต้องใส่รูปแบบอะไร? ต้องระบุปีหรือไม่?+
YearEndDate เป็น string literal ที่ใส่เป็นรูปแบบ "MM/DD" เช่น "06/30" หรือ "03/31" โดยไม่ต้องระบุปี เพราะ DAX จะละเว้นปีโดยอัตโนมัติ
สามารถเขียนได้หลายรูปแบบเช่น "6/30", "Jun 30", "30 June" ตาม locale ของ workbook แต่แนะนำให้ใช้ "MM/DD" เพื่อความชัดเจนครับ
ถ้า Date table ไม่มีวันที่บางวัน TOTALYTD จะทำงานถูกต้องไหม?+
Date table ต้องมีวันที่ต่อเนื่องครบถ้วนตลอดทั้งปี (ไม่มีวันที่ขาดหาย) เพื่อให้ time intelligence function เช่น TOTALYTD, DATESYTD และ CALCULATE ทำงานได้ถูกต้อง
หากวันที่ขาดหายหรือไม่ต่อเนื่อง ผลลัพธ์อาจคำนวณผิดพลาดหรือข้ามช่วงเวลาไป ควร mark as date table ในโมเดลเพื่อให้ DAX engine ตรวจสอบ continuity ได้ครับ 💡
สามารถใช้ TOTALYTD ร่วมกับ CALCULATE ได้หรือไม่?+
ได้ แต่ไม่แนะนำให้ nest TOTALYTD ภายใน CALCULATE เพราะ TOTALYTD เองก็เป็น wrapper ของ CALCULATE อยู่แล้ว
การ nest อาจทำให้เกิด filter context ที่ซับซ้อนและผลลัพธ์ผิดพลาด ถ้าต้องการ filter เพิ่มเติม แนะนำให้ใช้ Filter argument ของ TOTALYTD แทน หรือใช้ CALCULATE ร่วมกับ DATESYTD โดยตรงครับ
TOTALYTD ใช้กับ calculated column ได้หรือไม่?+
สามารถใช้ได้ แต่ไม่แนะนำเพราะ calculated column คำนวณที่ row context และจะคำนวณ YTD สำหรับทุกแถว ซึ่งอาจไม่ได้ผลลัพธ์ตามที่ต้องการและใช้ memory มาก
แนะนำให้ใช้ TOTALYTD ใน measure เท่านั้น เพื่อให้คำนวณ dynamic ตาม filter context และประหยัด memory ครับ 😎
ถ้าต้องการ YTD สำหรับปีที่แล้วต้องทำอย่างไร?+
ใช้ CALCULATE ร่วมกับ SAMEPERIODLASTYEAR เพื่อเลื่อน filter context ไปปีที่แล้ว เช่น CALCULATE(TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]), SAMEPERIODLASTYEAR('Date'[Date]))
จะได้ยอดสะสม YTD ของปีที่แล้วในช่วงเดือนเดียวกัน ส่วนตัวผมใช้บ่อยมากตอนต้องเปรียบเทียบ year-over-year growth ครับ 💡
TOTALYTD สามารถใช้กับ fiscal quarter ที่เริ่มไม่ใช่วันแรกของเดือนได้หรือไม่?+
YearEndDate ของ TOTALYTD รองรับเฉพาะการกำหนดวันสิ้นปี ไม่รองรับการกำหนด fiscal quarter start date ที่ไม่ใช่วันแรกของเดือน
ถ้าต้องการใช้ fiscal calendar แบบ 4-4-5 หรือ ISO calendar แนะนำให้สร้าง custom calendar table และใช้ CALCULATE ร่วมกับ DATESBETWEEN หรือใช้ calendar-based time intelligence functions แทนครับ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ TOTALYTD จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
DATESBETWEEN คืนตารางวันที่ที่อยู่ระหว่าง StartDate และ EndDate (รวมวันเริ่ม/วันจบ) เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อคำนวณตามช่วงวันที่ที่กำหนดเอง
DATESYTD คืนตารางวันที่ของช่วงตั้งแต่ต้นปีถึงวันที่สุดท้ายที่มองเห็นใน Filter Context ใช้เป็นตัวกรองเวลาใน CALCULATE สำหรับการคำนวณยอดสะสมตั้งแต่ต้นปี รองรับทั้งปีปฏิทินและปีบัญชี (Fiscal Year) ด้วย YearEndDate argument
ENDOFYEAR คืนตารางวันที่ของวันสิ้นปีตามบริบทวันที่ปัจจุบัน และรองรับการกำหนดวันสิ้นปีบัญชี เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อประเมินมาตรวัดที่วันสิ้นปี
OPENINGBALANCEYEAR คำนวณค่า Expression ณ วันแรกของปี โดยดึงค่าจากวันสิ้นปีปีก่อนหน้า เหมาะสำหรับการหา opening balance ของคงเหลือ บัญชี สต็อก และค่าที่วัดได้แค่ที่จุดเวลาเดียว
PARALLELPERIOD คืนตารางวันที่ที่ขนานกับช่วงเวลาปัจจุบัน แต่เลื่อนไปข้างหน้าหรือย้อนหลัง และขยายขอบเขตให้ครอบคลุมทั้งหน่วยเวลาที่ระบุ (MONTH/QUARTER/YEAR) เสมอ เหมาะกับการเทียบยอดรวมทั้งเดือน/ไตรมาส/ปี
PREVIOUSYEAR คืนตารางวันที่ของปีก่อนหน้า ตามบริบทวันที่ปัจจุบัน เหมาะสำหรับตัวชี้วัดแบบ Year-over-Year โดยนำตารางวันที่ไปใช้เป็นตัวกรองเวลา
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
STARTOFYEAR คืนตาราง 1 แถวที่มีวันที่วันแรกของปีในบริบทปัจจุบัน ใช้สำหรับ semi-additive measure เช่น inventory balance หรือเปรียบเทียบ year-to-date
คำนวณยอดสะสมตั้งแต่ต้นเดือน (Month-to-Date) ของ Expression ตามคอลัมน์วันที่ที่กำหนด เป็นทางลัดของ CALCULATE + DATESMTD
TOTALQTD คำนวณยอดสะสมตั้งแต่วันแรกของไตรมาส (Q1/Q2/Q3/Q4) จนถึงวันที่ล่าสุดใน filter context ปัจจุบัน
.
ส่วนตัวผมใช้บ่อยมากตอน analyze quarterly performance และการวิเคราะห์ KPI รายไตรมาสครับ
คำนวณยอดสะสมตั้งแต่ต้นสัปดาห์ (Week-to-Date) ของ Expression ตามคอลัมน์วันที่ที่กำหนด โดยรวมข้อมูลจากวันแรกของสัปดาห์จนถึงวันปัจจุบันในบริบท
ALL มีพฤติกรรมแตกต่างกัน 2 แบบ: ใช้เป็น Table Expression จะคืนทุกแถวในตาราง หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter ใดๆ เหมาะสำหรับใช้ร่วมกับ FILTER, COUNTROWS, SUMMARIZE หรือใช้เป็น CALCULATE Modifier เพื่อลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ทำให้สามารถคำนวณ Grand Total หรือหาเปอร์เซ็นต์เทียบกับยอดรวมได้
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
อีเมลของคุณจะไม่ถูกเผยแพร่