Thep Excel
DATESMTDชุดวันที่ตั้งแต่ต้นเดือนถึงปัจจุบัน (MTD)
DATESMTD คืนตารางวันที่ของช่วงตั้งแต่ต้นเดือนถึงวันที่ล่าสุดใน filter context เหมาะกับการทำยอดสะสมตั้งแต่ต้นเดือน (MTD) โดยใช้ร่วมกับ CALCULATE และควรใช้คู่กับ ShowValueForDates pattern เพื่อไม่ให้แสดงค่าในวันที่ยังไม่มีข้อมูล
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| Dates | column | คอลัมน์วันที่จากตารางวันที่ (Date table) ที่ถูก mark เป็น Date table แล้ว ต้องเป็นคอลัมน์ที่มีวันที่ครบถ้วนตั้งแต่ 1 ม.ค. ถึง 31 ธ.ค. ของทุกปีที่มีข้อมูล |
Additional Notes
DATESMTD คืนค่าเป็นตารางของวันที่ตั้งแต่วันแรกของเดือน จนถึงวันที่ล่าสุดใน filter context ปัจจุบัน ซึ่งเป็นพื้นฐานสำคัญสำหรับการคำนวณยอดสะสมตั้งแต่ต้นเดือน (Month-to-Date หรือ MTD)
.
ที่เจ๋งคือ DATESMTD ทำงานคล้ายกับ DATESYTD แต่ scope จำกัดอยู่ในเดือนเดียว ซึ่งเหมาะกับการติดตาม KPI รายเดือน เช่น ยอดขายสะสมตั้งแต่ต้นเดือน หรือจำนวนออเดอร์สะสมที่ต้องดูความคืบหน้าระหว่างเดือน
.
ส่วนตัวผมแนะนำให้ใช้ DATESMTD ร่วมกับ ShowValueForDates pattern เพื่อไม่ให้แสดงค่าในวันที่ยังไม่มีข้อมูล เช่น วันที่ในอนาคต วิธีนี้จะทำให้รายงานดูเป็นมืออาชีพมากขึ้นครับ 😎
การทำงานเบื้องหลัง
DATESMTD(Dates) มีการทำงานเทียบเท่ากับสูตร:
DATESBETWEEN(
Dates,
STARTOFMONTH(LASTDATE(Dates)),
LASTDATE(Dates)
)ซึ่งหมายความว่ามันจะหาวันแรกของเดือนจากวันที่ล่าสุดใน filter context แล้วสร้างตารางวันที่ตั้งแต่วันแรกของเดือนจนถึงวันที่ล่าสุดนั้น
ShowValueForDates Pattern
เพื่อป้องกันการแสดงผลลัพธ์ในวันที่ที่ยังไม่มีข้อมูล (เช่น วันที่ในอนาคต) ให้สร้าง Measure ช่วยดังนี้:
ShowValueForDates =
VAR LastDateWithData =
CALCULATE(
MAX(Sales[OrderDate]),
REMOVEFILTERS()
)
VAR FirstDateVisible = MIN('Date'[Date])
VAR Result = FirstDateVisible <= LastDateWithData
RETURN
Result.
แล้วนำไปใช้ร่วมกับ Sales MTD ดังนี้:
Sales MTD =
IF(
[ShowValueForDates],
CALCULATE(
[Sales Amount],
DATESMTD('Date'[Date])
)
)Pattern นี้มาจาก DAXPatterns.com ซึ่งเป็นแหล่งอ้างอิงหลักสำหรับ DAX Patterns ครับ 💡
How it works
คำนวณยอดขายสะสมตั้งแต่ต้นเดือน
ใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อให้ Measure คำนวณเฉพาะช่วง MTD สำหรับติดตามเป้าหมายรายเดือน
เปรียบเทียบ MTD กับ MTD เดือนก่อน
ใช้ร่วมกับ DATEADD เพื่อเปรียบเทียบยอด MTD ปัจจุบันกับยอด MTD ของเดือนก่อนหน้า
ติดตาม KPI รายเดือน
เช่น จำนวนออเดอร์สะสม หรือจำนวนลูกค้าใหม่สะสมตั้งแต่ต้นเดือน เพื่อดูความคืบหน้าระหว่างเดือน
สร้างยอดสะสมรายเดือนที่ซ่อนวันที่ในอนาคต
ใช้ร่วมกับ ShowValueForDates pattern เพื่อไม่แสดงค่าในวันที่ที่ยังไม่มีข้อมูลจริง
Examples
ตัวอย่างที่ 1: ยอดขายสะสมตั้งแต่ต้นเดือน (พื้นฐาน)Sales MTD = CALCULATE( [Sales Amount], DATESMTD('Date'[Date]) )
DATESMTD สร้างตารางวันที่ตั้งแต่วันที่ 1 ของเดือนจนถึงวันที่ล่าสุดใน filter context แล้ว CALCULATE ใช้ตารางนี้เป็นตัวกรองเพื่อคำนวณ Sales Amount เฉพาะช่วงนั้น ถ้าเลือกวันที่ 15 มิ.ย. ก็จะได้ยอดรวม 15 วันแรกของเดือน
ตัวอย่างที่ 2: Sales MTD พร้อม ShowValueForDates Pattern— Measure ช่วยเช็ควันที่มีข้อมูล (ซ่อนไว้) ShowValueForDates = VAR LastDateWithData = CALCULATE( MAX(Sales[OrderDate]), REMOVEFILTERS() ) VAR FirstDateVisible…
ShowValueForDates ตรวจสอบว่าวันที่แรกที่มองเห็นใน filter context อยู่ก่อนหรือเท่ากับวันที่มีข้อมูลล่าสุดหรือไม่ ถ้าใช่จึงคำนวณ MTD ถ้าไม่ใช่จะคืน BLANK Pattern นี้มาจาก DAXPatterns.com ซึ่งช่วยให้รายงานดูเป็นมืออาชีพมากขึ้นครับ 💡
ตัวอย่างที่ 3: เปรียบเทียบ MTD vs Previous MTD— MTD ปัจจุบัน Sales MTD = CALCULATE( [Sales Amount], DATESMTD('Date'[Date]) ) — MTD เดือนก่อน (PMTD) Sales PMTD = CALCULATE( [Sales MTD], DATEADD('Date'[Date…
ที่เจ๋งคือ DATEADD shift วันที่กลับไป 1 เดือน แต่ยังคง range เดิมไว้ ทำให้เปรียบเทียบได้ยุติธรรม เช่น ถ้าเดือนนี้มีข้อมูลแค่ 15 วัน ก็จะเทียบกับ 15 วันแรกของเดือนก่อนเท่านั้น ไม่ใช่เทียบกับทั้งเดือน 😎
ตัวอย่างที่ 4: คำนวณ MTD Growth %MTD Growth % = VAR CurrentMTD = [Sales MTD] VAR PreviousMTD = [Sales PMTD] VAR Growth = DIVIDE( CurrentMTD – PreviousMTD, PreviousMTD, BLANK() ) RETURN Growth
ใช้ VAR เก็บค่า MTD ปัจจุบันและเดือนก่อน แล้วคำนวณ % การเติบโต DIVIDE ช่วยจัดการกรณีหารด้วยศูนย์โดยคืน BLANK แทน Error ส่วนตัวผมชอบใช้ pattern นี้มากเพราะอ่านง่ายและแก้ไขง่ายครับ
ตัวอย่างที่ 5: MTD กับ Fiscal CalendarSales MTD Fiscal = CALCULATE( [Sales Amount], DATESMTD(FiscalCalendar) )
DATESMTD รองรับ Calendar reference (ไม่ใช่แค่ Date column) ทำให้สามารถใช้กับ Fiscal Calendar ที่เริ่มเดือนไม่ตรงกับปฏิทินปกติได้ เช่น ปีงบประมาณที่เริ่ม 1 ตุลาคม
ตัวอย่างที่ 6: ใช้ DATESMTD เพื่อดูตารางวันที่ (Debug)MTD Dates Debug = DATESMTD('Date'[Date])
เนื่องจาก DATESMTD คืนค่าเป็นตาราง จึงใช้ดูว่าช่วงวันที่ที่ถูกเลือกคืออะไรได้ ซึ่งมีประโยชน์ตอน debug หรือตรวจสอบว่า filter context ทำงานตามที่คาดไว้หรือไม่
ตัวอย่างที่ 7: Running Total ภายในเดือนDaily Running Total (Month) = CALCULATE( [Sales Amount], DATESMTD('Date'[Date]) )
เมื่อใช้ใน Matrix หรือ Table ที่มี Date อยู่ใน row แต่ละแถวจะแสดงยอดสะสมตั้งแต่ต้นเดือนถึงวันที่ในแถวนั้น ซึ่งเป็น Running Total ที่ reset ทุกต้นเดือน Pattern นี้มีประโยชน์มากสำหรับการติดตามเป้าหมายรายเดือนครับ 💡
FAQs
DATESMTD คืนค่าเป็นตารางหรือค่าสเกลาร์?+
คืนค่าเป็นตารางของวันที่ (Table) จึงมักใช้เป็นตัวกรองภายใน CALCULATE เพื่อให้ได้ค่าสเกลาร์จาก Measure ถ้าใช้เดี่ยวๆ จะได้ตารางที่มีคอลัมน์เดียวคือวันที่ในช่วง MTD
ทำไม DATESMTD แล้วได้ค่า BLANK?+
ปัญหานี้เจอบ่อยครับ 😅 สาเหตุหลักๆ คือ: 1) ตาราง Date ไม่ได้ถูก mark เป็น Date table 2) คอลัมน์วันที่มีวันที่ไม่ต่อเนื่อง (ต้องมีทุกวันตั้งแต่ 1 ม.ค. ถึง 31 ธ.ค.) 3) มี relationship ไม่ถูกต้องระหว่าง Date table กับ Fact table
DATESMTD ต่างจาก TOTALMTD อย่างไร?+
DATESMTD คืนตารางวันที่ ส่วน TOTALMTD คืนค่าผลลัพธ์เลย ใช้ DATESMTD เมื่อต้องการความยืดหยุ่นมากกว่า เช่น ใช้ร่วมกับ IF หรือ filter อื่นๆ ใน CALCULATE ส่วน TOTALMTD เหมาะกับการใช้งานง่ายๆ ที่ต้องการผลลัพธ์ตรงๆ
ทำไมต้องใช้ ShowValueForDates pattern?+
เพื่อไม่ให้แสดงค่าในวันที่ที่ยังไม่มีข้อมูลจริง เช่น ถ้าวันนี้คือ 15 มิ.ย. แต่รายงานแสดงทั้งเดือน วันที่ 16-30 ยังไม่มีข้อมูล ถ้าไม่ใช้ pattern นี้อาจแสดงค่า 0 หรือค่าผิดพลาด ซึ่งทำให้รายงานดูไม่เป็นมืออาชีพครับ
เปรียบเทียบ MTD vs Previous MTD ต้องใช้ DATEADD หรือ PARALLELPERIOD?+
ใช้ DATEADD ครับ เพราะ DATEADD จะ shift วันที่แบบ day-by-day ทำให้ช่วงวันที่ตรงกัน เช่น 1-15 มิ.ย. เทียบกับ 1-15 พ.ค. ส่วน PARALLELPERIOD จะคืนทั้งเดือนก่อนหน้า ซึ่งไม่เหมาะกับการเปรียบเทียบ MTD vs PMTD
DATESMTD ใช้กับ DirectQuery ได้ไหม?+
ได้ครับ แต่มีข้อจำกัด: ไม่รองรับใน calculated columns หรือ Row-Level Security (RLS) rules ใน DirectQuery mode ใช้ได้เฉพาะใน Measures และ Calculated tables
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ DATESMTD จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CLOSINGBALANCEMONTH ประเมิน Expression ณ วันสิ้นเดือนของบริบทวันที่ปัจจุบัน เหมาะกับการทำมาตรวัดยอดคงเหลือปลายเดือน และรองรับการใส่ตัวกรองเพิ่มเติม
DATEADD คืนค่าตารางวันที่ ที่ถูกเลื่อนออกไปจากช่วงวันที่ใน Context ปัจจุบัน ตามจำนวน (NumberOfIntervals) และหน่วย (Interval) ที่กำหนด เช่น เลื่อนย้อนหลัง 1 เดือน หรือเลื่อนไปข้างหน้า 1 ปี
DATESBETWEEN คืนตารางวันที่ที่อยู่ระหว่าง StartDate และ EndDate (รวมวันเริ่ม/วันจบ) เหมาะกับการใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อคำนวณตามช่วงวันที่ที่กำหนดเอง
DATESINPERIOD คืนตารางวันที่แบบเคลื่อนที่ โดยเริ่มจาก StartDate และขยายไปตามจำนวนช่วงและหน่วยที่กำหนด เหมาะกับการทำคำนวณแบบ rolling YTD, rolling average, ย้อนหลัง หรือล่วงหน้าแบบยืดหยุ่น
DATESQTD คืนตารางวันที่ของช่วงตั้งแต่ต้นไตรมาสถึงวันที่ล่าสุดในบริบทปัจจุบัน เหมาะสำหรับสร้างมาตรวัด QTD ใน CALCULATE โดยทำงานร่วมกับ Time Intelligence Functions ตัวอื่นๆ ได้ดี และควรใช้คู่กับ ShowValueForDates pattern เพื่อป้องกันการแสดงค่าในอนาคต
DATESWTD คืนตารางวันที่ตั้งแต่ต้นสัปดาห์จนถึงวันที่ในบริบทปัจจุบัน ใช้เป็นตัวกรองเวลาใน CALCULATE เพื่อคำนวณยอดสะสมตั้งแต่ต้นสัปดาห์
DATESYTD คืนตารางวันที่ของช่วงตั้งแต่ต้นปีถึงวันที่สุดท้ายที่มองเห็นใน Filter Context ใช้เป็นตัวกรองเวลาใน CALCULATE สำหรับการคำนวณยอดสะสมตั้งแต่ต้นปี รองรับทั้งปีปฏิทินและปีบัญชี (Fiscal Year) ด้วย YearEndDate argument
ENDOFMONTH คืนตารางวันที่ของวันสิ้นเดือนตามปฏิทิน เหมาะกับ Semi-Additive Calculations เช่น ยอดคงเหลือ, Inventory, หรือ Closing Balance ที่ต้องการ snapshot ณ สิ้นเดือน ไม่ว่าจะมีข้อมูลจริงในวันนั้นหรือไม่
LASTDATE คืนค่าตารางที่มีวันที่สุดท้ายในบริบทตัวกรองปัจจุบัน มักใช้เป็นตารางตัวกรองให้ CALCULATE เพื่อคำนวณ measure ณ วันสุดท้ายของช่วงที่เลือก และสามารถใช้ MAXX เพื่อแปลงผลเป็น scalar date ได้
OPENINGBALANCEMONTH ประเมิน Expression ณ จุดเปิดเดือน (วันสิ้นเดือนก่อนหน้า) เหมาะสำหรับหา opening balance ของยอดคงเหลือ สต็อก หรือสถิติ ณ จุดเริ่มเดือน
PARALLELPERIOD คืนตารางวันที่ที่ขนานกับช่วงเวลาปัจจุบัน แต่เลื่อนไปข้างหน้าหรือย้อนหลัง และขยายขอบเขตให้ครอบคลุมทั้งหน่วยเวลาที่ระบุ (MONTH/QUARTER/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
STARTOFMONTH คืนวันแรกของเดือนเป็นตาราง 1 แถว ใช้กับการวัดที่ขึ้นต่อจุดเวลา เช่น ยอดคงเหลือ บัญชี หรือสินค้าคงคลัง
คำนวณยอดสะสมตั้งแต่ต้นเดือน (Month-to-Date) ของ Expression ตามคอลัมน์วันที่ที่กำหนด เป็นทางลัดของ CALCULATE + DATESMTD
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
อีเมลของคุณจะไม่ถูกเผยแพร่