Power BI ตอนที่ 09: สร้าง Date Table ด้วย DAX

ในตอนที่แล้วเราได้สร้าง Data Model ให้เป็น Star Schema ได้แล้ว เหลืออีกขั้นตอนนึงในการทำให้ Data Model ของเราสมบูรณ์ยิ่งขึ้น นั่นก็คือการสร้าง Date Table นั่นเอง

แม้ว่าในความเป็นจริงแล้วเราสามารถลาก Field วันที่จาก Fact Table ลงไปใน Visual ได้เลย แต่ทว่านั่นเป็นสิ่งที่เหล่าผู้เชี่ยวชาญไม่แนะนำให้ทำ เพราะมันจะสร้างตารางวันที่จำลองที่มองไม่เห็นขึ้นมาเต็มไปหมด วิธีที่ถูกต้องคือสร้าง Dimension Table ที่เป็นตารางวันที่ขึ้นมาต่างหาก

การสร้าง Date Table นอกจากจะลดปัญหาที่บอกข้างบนแล้ว ยังช่วยให้ฟังก์ชัน DAX ตระกูล Time Intelligence ทั้งหมดทำงานได้อย่างถูกต้องอีกด้วย เช่นการคิดเลขสะสมตั้งแต่ต้นปี (Year-to-Date) หรือ การคิดเทียบกับช่วงเดียวกันในปีที่แล้ว (Same period last year) แต่ว่าการเขียนสูตรพวก Time Intelligence เราจะยังไม่พูดถึงมันตอนนี้นะ

บทความนี้จะเป็นการสอนทีละ Step ส่วนใครอยากใช้สูตรสำเร็จรูป สร้าง Date Table พรวดเดียวทุกคอลัมน์เลย ให้ทำตามบทความอีกอันนึงคืออันนี้ครับ

เริ่มสร้าง Date Table ด้วย DAX แบบ New Table

การจะสร้างตารางขึ้นมาใหม่ด้วย DAX (ซึ่งก็คือการเขียนสูตรใน Power BI) เราต้องเลือกคำสั่ง ใน Ribbon Modeling -> New Table ขึ้นมา

วิธีการเขียนสูตรคือ

ใส่ชื่อตาราง=สูตร

Date Table แบบง่าย ด้วย CALENDARAUTO

dDate = CALENDARAUTO()

เขียนสูตรข้างบนแล้วกด Enter เลย

การเขียนสูตรแบบนี้ Power BI จะสร้างตารางชื่อ dDate ออกมาแบบนี้

โดยที่

• วันเริ่มต้น คือ วันที่ 1 มกราคม ของปีเดียวกับวันที่ที่น้อยสุดใน Data เรา
• วันสิ้นสุด คือ วันที่ 31 ธันวาคม ของปีเดียวกับวันที่ที่มากสุดใน Data เรา

ซึ่งสูตรนี้จะใช้ได้ดี กรณีที่วันที่ใน Data เรามีแต่เรื่องที่เราต้องการ เช่น เป็นวันซื้อขายของ

แต่ถ้าใน Data เราดันมีวันที่แปลกๆ ปนมา เช่น วันเกิดพนักงาน หรือ วันก่อตั้งบริษัท มันอาจจะได้วันที่เริ่มต้นที่เก่ามากๆ มาก็ได้ ซึ่งจะได้เยอะเกินความจำเป็นไป

สร้าง Date Table ตามวันที่เราต้องการ

ถ้าเราอยากจะกำหนดวันเอง เราจะใช้ฟังก์ชันชื่อ CALENDAR เฉยๆ โดยที่จะต้องระบุวันเริ่มกับวันสิ้นสุด โดยมีรูปแบบดังนี้

dDate = CALENDAR ( <StartDate>, <EndDate> )

ซึ่งในความเป็นจริงแล้วเราก็สามารถเขียนสูตรเพื่อหาวันเริ่มกับวันสิ้นสุดที่เหมาะสมได้ เช่น

= CALENDAR ( 
DATE ( ปีเริ่ม, 1, 1 ), 
DATE ( ปีจบ, 12, 31 ) 
)

ซึ่งปีเริ่ม เราอาจหาจาก YEAR(MIN(fSales[DateKey])) ได้
และปีจบคือ YEAR(MAX(fSales[DateKey]))

Tips : วิธีมาตรฐานที่ดีในการเขียนสูตร DAX

• อ้างอิง Column : อ้างอิงด้วย ‘TableName'[ColumnName]
• อ้างอิงMeasure : อ้างอิงด้วย [MeasureName]

นี่คือสาเหตุที่เราอ้างอิง Datekey ใน fSales ด้วย fSales[DateKey] นะครับ

และตอนเขียนสูตรจริงๆ เราสามารถพิมพ์ชื่อตารางก่อน แล้วมันจะ list คอลัมน์ของตารางนั้นมาให้เลือกเองครับ แบบนี้จะสะดวกกว่า

ซึ่งพอทุกอย่างรวมกันจะเขียนได้ว่า

dDate = 
CALENDAR (
DATE ( YEAR(MIN(fSales[DateKey])), 1, 1 ),
DATE ( YEAR(MAX(fSales[DateKey])), 12, 31 )
)

การตั้งตัวแปรขึ้นมาใน DAX

โดยที่หากเราต้องการให้สูตรอ่านง่ายขึ้น เราสามารถใช้ VAR มาตั้งเป็นตัวแปร และใช้ RETURN เพื่อบอกว่าต้องการจะแสดงผลลัพธ์อะไรได้ ในรูปแบบว่า

VAR ตัวแปร1=สูตรตัวแปร1
VAR ตัวแปร2=สูตรตัวแปร2
RETURN สูตรผลลัพธ์

เช่น

dDate = 
VAR myStartDate=DATE ( YEAR(MIN(fSales[DateKey])), 1, 1 )
VAR myEndDate=DATE ( YEAR(MAX(fSales[DateKey])), 12, 31 )
RETURN
CALENDAR (myStartDate,myEndDate)

Tips1 : เราสามารถทำให้สูตรอ่านง่ายขึ้น ด้วยการ copy สูตรไปใส่เว็บ DAX Formatter

Tips2 : ถึงมันจะใช้คำว่า VAR ที่มาจากคำว่า Variable ที่แปลว่าตัวแปร แต่ในความเป็นจริงมันไม่ได้เป็นตัวแปรแบบภาษา Computer ปกติ แต่ว่ามันจะคำนวณค่าจาก สูตรตัวแปร1 เก็บไว้ใน ตัวแปร1 ในลักษณะที่เป็นค่าคงที่

นั่นคือจะบันทึกเป็นค่า Constant โดยจะถูก Evaluate หรือคำนวณครั้งเดียวตอนที่ประกาศใน VAR (ไม่ได้คำนวณขณะตอนถูกเรียกใช้ใน RETURN)

คราวนี้เราจะได้คอลัมน์ Date ขึ้นมาคอลัมน์นึงแล้ว ต่อไปเราจะใช้ DAX สร้างคอลัมน์อื่นๆ ขึ้นมานะครับ ซึ่งต่อไปน่าจะง่ายกว่าสร้างตารางแล้วครับ

Mark as Date Table

เพื่อให้ตารางที่สร้างขึ้นมาใหม่ สามารถทำงานรองรับฟังก์ชัน Time Intelligence ได้ เราจะต้องมีการระบุให้ชัดเจนว่าตารางนี้คือ Date Table อย่างเป็นทางการด้วย

หลังจากนั้นให้เลือกว่าคอลัมน์ไหนคือวันที่ (ซึ่งก็มีอยู่อันเดียวคือ Date)

Power BI จะทำการตรวจสอบว่าคอลัมน์วันที่ของเราถูกต้องหรือไม่ ซึ่งจะต้องมีวันครบทุกวันเรียงต่อกัน และมีค่าไม่ซ้ำกันด้วย โดยที่ถ้าเราสร้าง Table นี้มาจาก DAX ยังไงก็ไม่พลาดหรอก ก็จะสามารถกด ok ได้เลย

สร้างคอลัมน์เพิ่มเติมด้วย DAX แบบ New Column

จริงๆ แล้วแค่ Mark as Date Table ก็สามารถทำให้เราใช้ Time Intelligent ได้แล้ว แต่เราจะสร้างคอลัมน์เพิ่ม เพื่อให้มี Dimension ต่างๆ เอาไปใช้ใน Visual เพิ่มเติม เช่น ปี ไตรมาศ เดือน วัน วันประจำสัปดาห์ เป็นต้น

เริ่มจากตัวง่ายสุดคือปี ซึ่งเราจะกด New Column

แล้วเขียนสูตรว่า

Year=YEAR(dDate[date])

ซึ่ง dDate[Date] ก็คือการอ้างอิงคอลัมน์ Date ในตาราง dDate นั่นเอง

แค่นี้เราก็จะมีคอลัมน์ Year เพิ่มขึ้นมา เหมือนกับการสร้างคอลัมน์ใน Table ของ Excel เลย

จากนั้นเราก็ใส่คอลัมน์เพิ่มเรื่อยๆ ดังนี้

• MonthNum = MONTH(dDate[Date])
• DayNum = DAY(dDate[Date])

ซึ่งจะเห็นว่าฟังก์ชันพวกนี้เหมือนกับใน Excel เลย

อย่างไรก็ตามใน DAX ก็มีหลายฟังก์ชันที่คล้ายๆ กับ Excel แต่ก็ไม่เหมือนเป๊ะ เช่น ใน Excel เราสามารถใช้ฟังก์ชัน TEXT เพื่อเปลี่ยนวันที่เป็นชื่อเดือนหรือชื่อวันได้ แต่ใน DAX เราจะใช้ฟังก์ชันชื่อว่า FORMAT แทน (เปลี่ยนแค่ชื่อฟังก์ชันแหละ)

• MonthName = FORMAT(dDate[Date],”mmmm”)
• DayName = FORMAT(dDate[Date],”dddd”)
• YearMonth = FORMAT(dDate[Date],”yyyymm”)

แถมฟังก์ชัน FORMAT ยังสามารถรองรับการหา Quarter ได้ด้วยตัว q เลย ไม่ต้องมานั่ง ROUNDUP เหมือนใน Excel

• Quarter = FORMAT(dDate[Date],”q”)

ถ้าเขียนแค่นี้ก็จะออกมาเป็น text คำว่า 1-4 เลย แต่ถ้าอยากให้มีตัว Q ด้วย (จะได้ดูดีขึ้น) ก็สามารถใช้ & มาช่วยธรรมดาๆ เช่น

• Quarter = “Q”&FORMAT(dDate[Date],”q”)

สรุปแล้ว ณ ตอนนี้เรามีคอลัมน์ดังนี้

สรุปสูตรทั้งหมด

New Table

dDate =
VAR myStartDate=DATE ( YEAR(MIN(fSales[DateKey])), 1, 1 )
VAR myEndDate=DATE ( YEAR(MAX(fSales[DateKey])), 12, 31 )
RETURN
CALENDAR (myStartDate,myEndDate)

New Column

• Year = YEAR(dDate[Date])
• MonthNum = MONTH(dDate[Date])
• DayNum = DAY(dDate[Date])
• MonthName = FORMAT(dDate[Date],”mmmm”)
• DayName = FORMAT(dDate[Date],”dddd”)
• YearMonth = FORMAT(dDate[Date],”yyyymm”)

ลาก Relationship เพิ่ม

step ต่อไปเราจต้องสร้าง Relationship ระหว่างตาราง dDate กับ fSales ทั้งนี้เพราะว่าชื่อคอลัมน์มันไม่ตรงกันนั่นเอง แต่การสร้าง Relationship ก็ไม่ยากเลย แค่อยู่ในหน้า Model แล้วลากจาก Field วันที่ 2 อันเชื่อมกัน (ลากจากตาราง fSales ไป dDate หรือจาก dDate ไป fSales ก็ได้)

ลองสร้าง Visual

สมมติว่าเราอยากจะแสดงยอดจำนวนสินค้า (Sales Quantity) รายเดือน เราก็เลยใส่ MonthName ลงไปใน Table

สิ่งที่เกิดขึ้นก็คือ ชื่อเดือนดันเรียงตามตัวอักษร! ไม่ได้เรียงตามลำดับเดือนตามปฏิทินที่ควรจะเป็น ซึ่งไม่มีใครในโลกเค้าเรียงแบบนี้กัน งงตายพอดี

Sort by Column

วิธีแก้ไข เราต้องกลับไปที่หน้า Data ของตาราง dDate แล้วไปกำหนดว่า MonthName จะให้มีการเรียงตาม Field ไหน ซึ่งวิธีทำคือให้เลือก Field MonthName ก่อน แล้วไปที่ Sort by Column จากนั้นเลือกว่าจะให้เรียงตามตัวไหน ซึ่งมันก็คือ MonthNum นั่นเอง

หลังจากกด Sort by Column แล้ว ผลลัพธ์ใน Visual ก็จะเปลี่ยนไปเรียงตามลำดับเดือนในปฏิทินทันทีครับ (ค่อยยังชั่ว)

สำหรับบทนี้จบเท่านี้ก่อน

ในตอนหน้าเราจะมาเรียนรู้การเขียน DAX Measure แบบพื้นฐาน เช่น SUMX, RELATED, DIVIDE กัน ซึ่งผมมองว่าตอนหน้าจะทำให้เปิดโลกคนใช้ Excel มาสู่คนใช้งาน DAX อย่างแท้จริงกันซักทีครับ

สารบัญ Series Power BI

• POWER BI ตอนที่ 01: POWER BI คืออะไร?
• POWER BI ตอนที่ 02: พื้นฐาน EXCEL ที่สำคัญก่อนจะเรียนรู้ POWER BI
• POWER BI ตอนที่ 03: ภาพรวมการใช้งาน POWER BI DESKTOP
• POWER BI ตอนที่ 04: สร้าง REPORT แรก ใน POWER BI
• POWER BI ตอนที่ 05: วิธีการ DRILL เพื่อเจาะลึกข้อมูลใน REPORT
• POWER BI ตอนที่ 06: การปรับแต่งสีใน VISUAL ด้วย CONDITIONAL FORMAT
• POWER BI ตอนที่ 07: เริ่ม GET DATA ตั้งแต่ไฟล์ยังว่างเปล่า
• POWER BI ตอนที่ 08: สร้าง DATA MODEL ที่เหมาะสม
• POWER BI ตอนที่ 09: สร้าง DATE TABLE ด้วย DAX
• POWER BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น
• POWER BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
• POWER BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน
• POWER BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
• Power BI ตอนที่ 14: Context Transition และ พลังแฝงใน Measure
• Power BI ตอนที่ 15: วิธีดึงค่าจาก Slicer มาคำนวณใน Report
• Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function
• Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual
• Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX
• Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
• ส่วนเสริม
• การคำนวณต้นทุนแบบ FIFO ด้วย DAX
• แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
• การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
• เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมีคอร์สออนไลน์ที่สอน Power BI ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่