“หวาว! เพิ่งรู้ว่า Power BI ทำแบบนี้ได้ด้วย” น้องนิจิยกสองมือประกบกัน
“ใช่ครับ ถ้าศึกษาสูตร DAX จะพบว่าประยุกต์ได้เยอะมาก” ผมส่งยิ้มหล่อที่สุดในชีวิต

เรื่องราวมีอยู่ว่า …

“พะ พี่คะ หนูขอเวลาสักครู่ได้ไหมคะ?” สาวน้อยคนหนึ่งถือคอมพิวเตอร์มาหาผมด้วยท่าทางเกร็ง ๆ
“ได้ครับ” ผมหันไปพบว่าเป็นน้องใหม่หน้าตาจิ้มลิ้มที่แก๊งหนุ่ม ๆ แอบพูดถึง
“นะ หนูชื่อนิจินะคะ พอดีพี่ตั้มแนะนำว่าอะไรเกี่ยวกับ Power BI ให้มาถามพี่”
“ถามได้ แต่อาจจะตอบไม่ได้นะครับ” ผมยิ้ม

“คือหนูอยากคลิก Slicer Month แล้วให้ตารางแสดงข้อมูล 6 เดือนย้อนหลัง เช่น คลิก Jun แสดงข้อมูล Jan – Jun คลิก Aug แสดงข้อมูล Mar – Aug Power BI ทำได้ไหมคะ?”
“ทำได้ครับ แต่มีเทคนิคนิดหน่อย”
“จริงหรือคะ!” น้องนิจิทำท่าดีใจ
“จริงครับ” ผมสังเกตว่าเธอดีใจแล้วน่ารัก

“แล้วถ้าหนูอยากให้มี Slicer เลือกจำนวนเดือนย้อนหลัง เช่น ถ้าคลิก 3 แสดงสามเดือนย้อนหลัง ถ้าคลิก 12 แสดงสิบสองเดือนย้อนหลังล่ะคะ?”
“ได้ครับ ใช้หลักการเดียวกันครับ”
“ถ้าไม่รบกวน พี่ช่วยสอนหนูได้ไหมคะ” น้องนิจิส่งสายตาวิงวอน ยิ่งดูน่ารัก อ๊ะ! ผมเผลอคิดว่าเธอน่ารักกี่ครั้งแล้วเนี่ย

สมมติมีตารางบันทึกการขายชื่อ tOrder มีคอลัมน์ OrderID, OrderDate และ Quantity หน้าตาแบบนี้

ให้สร้างตาราง Date เพื่อเป็น Dimension ของข้อมูลเกี่ยวกับวันที่ (เช่น ปี, เดือน) โดยคลิก Table tools/ New table

ใช้สูตร

tDate = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "MonthNum",MONTH([Date]),
    "Month",FORMAT([Date],"mmm"),
    "YearMonthNum",FORMAT([Date],"yyyymm"),
    "MonthYear",FORMAT([Date],"mmm-yy")
)

ได้ตารางชื่อ tDate หน้าตาแบบนี้

“ใช้สูตรเดียวได้ 6 คอลัมน์เลยหรือคะ?” น้องนิจิแปลกใจ
“ใช่ครับ เพราะใช้ฟังก์ชัน ADDCOLUMNS สามารถเพิ่มจำนวนคอลัมน์และกำหนดสูตรได้ตามต้องการครับ”

ปรับตารางนี้ให้เป็น Date Table โดยคลิก Table tools/ Mark as date table/ Mark as Date Table

จะมีหน้าต่างขึ้นมาถามว่า ใช้คอลัมน์อะไรเป็น Date column ให้เลือกคอลัมน์ Date จากนั้นคลิก OK

Note1: Date column ต้องเป็นคอลัมน์วันที่ที่ห้ามมีข้อมูลซ้ำ และห้ามเว้นวันใด ๆ (เช่น วันหยุด วันเสาร์-อาทิตย์)
Note2: ถ้าสร้างตาราง tDate ด้วยสูตรด้านบน จะได้คอลัมน์ Date ที่ตรงตามเงื่อนไขของ Date column

ปรับคอลัมน์ Month ให้เรียงข้อมูลตามคอลัมน์ MonthNum โดยคลิกคอลัมน์ Month แล้วเลือก Column tools/ Sort by column/ MonthNum

“ถ้าไม่ทำแบบนี้ เดือนจะถูกเรียงตามตัวอักษร A to Z หรือเรียงเป็น Apr, Aug, Dec ใช่ไหมคะ?”
“ถูกต้องครับ”

ปรับคอลัมน์ MonthYear ให้เรียงข้อมูลตามคอลัมน์ YearMonthNum โดยคลิกคอลัมน์ MonthYear แล้วเลือก Column tools/ Sort by column/ YearMonthNum

สร้างความสัมพันธ์ระหว่างตาราง tOrder โดยเชื่อม OrderDate กับ Date เข้าด้วยกัน

สร้างตารางชื่อ tMonthYear โดยคลิก New table

แล้วใช้สูตร

tMonthYear = 
SUMMARIZE(
    tDate,
    tDate[YearMonthNum],
    tDate[MonthYear]
)

ได้ตารางใหม่หน้าตาแบบนี้

ปรับคอลัมน์ MonthYear (ของตาราง tMonthYear) ให้เรียงข้อมูลตามคอลัมน์ YearMonthNum โดยคลิกคอลัมน์ MonthYear แล้วเลือก Column tools/ Sort by column/ YearMonthNum

สร้างตารางชื่อ tNum (เพื่อใช้เป็น Slicer เลือกจำนวนเดือนย้อนหลัง) โดยคลิก New table

แล้วใช้สูตร

tNum = GENERATESERIES(1,12)

ได้ผลลัพธ์เป็นตารางที่มีหนึ่งคอลัมน์ ข้อมูลในคอลัมน์คือตัวเลขตั้งแต่ 1 ถึง 12
เปลี่ยนชื่อคอลัมน์เป็น SelectN

“ว้าว! เพิ่งทราบว่าสร้างตารางโดยใช้สูตรได้ด้วย ที่ผ่านมาหนูใช้วิธี Enter Data ทีละค่าเลยค่ะ พูดแล้วอายจัง” น้องนิจิก้มหน้าเล็กน้อยแล้วพูดแบบเขิน ๆ
“ก่อนหน้านี้ผมก็ทำแบบนั้นครับ แต่พอรู้จักฟังก์ชัน GENERATESERIES ก็เลยใช้วิธีนี้แทน” ผมยิ้มตอบ

ไม่ต้องสร้างความสัมพันธ์กับตาราง tMonthYear และ tNum ตารางทั้งสองนี้ไม่ต้องเชื่อมต่อกับตารางใดเลย ศัพท์เทคนิคเรียกว่า Disconnected Table

นำคอลัมน์ MonthYear จากตาราง tMonthYear (ย้ำ! จากตาราง MonthYear) ไปสร้างเป็น Table

สร้าง Slicer จากคอลัมน์ [SelectN], [Year] และ [Month] แล้วจัดเรียงให้สวยงาม

สร้าง Measure โดยคลิก Home/ New measure

โดยใช้สูตร

LastNMonthQuantity = 
VAR LastN = SELECTEDVALUE(tNum[SelectN],3)*-1
VAR LastNPeriod = 
DATESINPERIOD(
    tDate[Date],
    LASTDATE(tDate[Date]),
    LastN,
    MONTH
)
VAR CurrentPeriod = SELECTEDVALUE(tMonthYear[MonthYear])
VAR CurrentPeriodQty = 
CALCULATE(
    SUM(tOrder[Quantity]),
    LastNPeriod,
    tDate[MonthYear]=CurrentPeriod
)
VAR SubTotalQty = 
CALCULATE(
    SUM(tOrder[Quantity]),
    LastNPeriod
)
RETURN
IF(
    HASONEVALUE(tMonthYear[MonthYear]),
    CurrentPeriodQty,
    SubTotalQty
)

นำ Measure ที่เพิ่งสร้างไปเพิ่มใน Table

เลือก Slicer แสดงผลได้ตามต้องการ

“หวาว! เพิ่งรู้ว่า Power BI ทำแบบนี้ได้ด้วย” น้องนิจิยกสองมือประกบกัน
“ใช่ครับ ถ้าศึกษาสูตร DAX จะพบว่าประยุกต์ได้เยอะมาก” ผมส่งยิ้มหล่อที่สุดในชีวิต

“ว่าแต่.. ทำไมถึงเขียนสูตรโดยใช้ VAR … RETURN … ล่ะคะ?”

เหตุผลที่ใช้ VAR … RETURN เพื่อเป็นการสร้างตัวแปร หรือปรับสูตรให้คำนวณเป็นสเต็ป ง่ายต่อการทำความเข้าใจหรือแก้ไข

จากสูตรเดิม ถ้ามองเป็นสเต็ปจะเป็นแบบนี้

LastNMonthQuantity = 
VAR LastN = Step1
VAR LastNPeriod = Step2
VAR CurrentPeriod = Step3
VAR CurrentPeriodQty = Step4
VAR SubTotalQty = Step5
RETURN Step6

ถ้ามองเป็นตรรกะ จะเป็นแบบนี้

LastNMonthQuantity = 
VAR LastN = รับค่าจาก Slicer tNum[SelectN]
VAR LastNPeriod = กำหนดช่วงเวลาย้อนหลังจำนวน N เดือน
VAR CurrentPeriod = รับค่าแต่ละบรรทัดจากตาราง tMonthYear
VAR CurrentPeriodQty = คำนวณ Last N Month Quantity ของแต่ละเดือน
VAR SubTotalQty = คำนวณ Last N Month Quantity สำหรับบรรทัด Sub Total
RETURN 
ถ้าเป็นข้อมูลรายเดือน แสดงผลเป็น CurrentPeriodQty 
ถ้าไม่ใช่ แสดงผลเป็น SubTotalQty

“ทำไมต้องระบุ 3 ในขั้นตอน LastN ด้วยล่ะคะ?” น้องนิจิเกิดคำถาม

LastN = SELECTEDVALUE(tNum[SelectN],3)*-1

“ทำเผื่อไว้เพื่อเป็นค่าดีฟอลต์ครับ เผื่อกรณียังไม่เลือก Slicer [SelectN] จะได้แสดงค่าดีฟอลต์ย้อนหลัง 3 เดือน” ผมตอบ
“งั้นถ้าไม่ใส่ 3 ในสูตรได้ไหมคะ?”
“ได้ครับ แต่ถ้าไม่เลือก Slicer [SelectN] จะไม่มีข้อมูลอะไรขึ้นมาเลย”
“ถ้าต้องการกำหนดดีฟอลต์ย้อนหลัง 6 เดือน ก็เปลี่ยนจาก 3 เป็น 6 ใช่ไหมคะ?”
“ถูกต้องครับ” ผมตอบ

“ขั้นตอน LastNPeriod มีความหมายว่ายังไงคะ?”

LastNPeriod = 
DATESINPERIOD(
    tDate[Date],
    LASTDATE(tDate[Date]),
    LastN,
    MONTH
)

LastNPeriod คือช่วงข้อมูลย้อนหลังจำนวน N เดือน
โดยถ้าคลิกเลือก Slicer SelectN เป็น 6 เลือกเดือนเป็น Aug เลือกปีเป็น 2020
ผลลัพธ์ของ LastNPeriod คือ 1-Mar-2020 ถึง 31-Aug-2020

“ที่ต้องสร้างตัวแปร CurrentPeriod = SELECTEDVALUE(tMonthYear[MonthYear]) เพื่อเป็นการระบุบรรทัดในตารางใช่ไหมคะ เช่น บรรทัดนั้นเป็น Jun-20, Jul-20 หรือ Aug-20″
“ถูกต้องครับ” ผมตอบ

“ขั้นตอน CurrentPeriodQty กับ SubTotalQty ต่างกันแค่ไม่มี tDate[MonthYear]=CurrentPeriod แท้จริงแล้วต่างกันยังไงคะ?”

VAR CurrentPeriodQty = 
CALCULATE(
    SUM(tOrder[Quantity]),
    LastNPeriod,
    tDate[MonthYear]=CurrentPeriod
)
VAR SubTotalQty = 
CALCULATE(
    SUM(tOrder[Quantity]),
    LastNPeriod
)

คำถามนี้ดีมาก อยากให้มองแบบนี้ครับ
ถ้าเขียนสูตรเป็น

CALCULATE(
    SUM(tOrder[Quantity]),
    LastNPeriod
)

ผลลัพธ์คือ Quantity ทั้งหมดใน Period นั้น ๆ (เช่น 6 เดือน)
ปัญหาคือ ผลลัพธ์จะเท่ากันทุกเดือน โดยไม่สนใจว่าบรรทัดนั้นคือเดือนอะไร
พูดง่าย ๆ คือ ผลลัพธ์ที่แสดงในเดือน Jun-20, Jul-20, Aug-20 จะเท่ากันและเท่ากับผลรวมของ N เดือน (เช่น 6 เดือน)
เพื่อให้แสดงผลลัพธ์ของแต่ละเดือนให้ถูกต้อง จึงต้องระบุเงื่อนไขว่าเป็นเดือนใด เงื่อนไขที่ว่าก็คือ
tDate[MonthYear]=CurrentPeriod

“ทำไมต้องใช้ IF( HASONEVALUE(tMonthYear[MonthYear]) ในสเต็ปสุดท้ายด้วยล่ะคะ?”

เพื่อให้แยกการคำนวณระหว่างข้อมูลบรรทัดที่เป็นเดือน กับบรรทัดที่เป็น Sub Total (รวมถึง Grand Total)
โดยถ้าข้อมูลเป็นเดือน ให้คำนวณเหมือนสเต็ป CurrentPeriodQty
แต่ถ้าเป็นบรรทัด Sub Total ให้คำนวณเหมือนสเต็ป SubTotalQty

“ที่ต้องสร้างตาราง tMonthYear ด้วยฟังก์ชัน SUMMARIZE เพราะถ้าใช้คอลัมน์ [MonthYear] จากตาราง tDate จะแสดงข้อมูลแค่เดือนเดียวใช่ไหมคะ?”
“ถูกต้องครับ”

ถ้าใช้คอลัมน์ [MonthYear] จากตาราง tDate สร้าง Table จะได้ผลลัพธ์เป็นแบบนี้

“งั้นถ้านิจิอยากให้มีตัวหนังสือแสดงว่าตอนนี้กำลังเลือกเดือนอะไร ปีอะไร แสดงข้อมูลกี่เดือนย้อนหลัง ต้องทำยังไงคะ?”
“ให้สร้าง Measure เพิ่ม แล้วใช้ Card แสดงผล Measure นั้นครับ” ผมยิ้มตอบ

สร้าง Measure ใหม่ คลิก Home/ New measure

ด้วยสูตร

ShowLastNMonth = 
"Show last "&
SELECTEDVALUE(tNum[SelectN])&
"-months quantity of "&
SELECTEDVALUE(tDate[MonthYear])

จากนั้นนำ Measure ที่สร้างใหม่ (ShowLastNMonth) ไปสร้างเป็น Card

ปรับหน้าตาให้สวยงาม

และนี่คือผลลัพธ์ของเราครับ

“ว้าว! เพิ่งรู้ว่า Power BI ทำได้ถึงขนาดนี้ ยอดมากเลยค่ะ” น้องนิจิปรบมือ
“ต้องใช้ Power BI ร่วมกับสูตร DAX ครับ ถ้าใช้ร่วมกันดี ๆ ทำได้แทบทุกอย่าง”
“ขอบคุณพี่มากเลยนะคะ” น้องนิจิโค้งขอบคุณ ยิ้มเธอช่างสดใส

ชื่อของเธอเป็นภาษาญี่ปุ่น เธอโค้งขอบคุณเหมือนคนญี่ปุ่น หรือว่าเธอเป็นลูกครึ่งญี่ปุ่น?
มิน่า ถึงขาวโอโม่ขนาดนั้น..

“นิจินี่ใคร?”ภรรยาผมถามหลังอ่านบทความ
“น้องที่ทำงาน” ผมตอบ
“แน่ใจ๊?”
“แน่ใจจ่ะ”
“อย่าให้รู้นะ!” ภรรยาสะบัดบ็อบใส่
“น้องจริง ๆ นอ-ออ-งอ-นอง-ไม้โท-น้อง น้องจริง ๆ” ผมตะโกนไล่หลัง ภรรยาเดินไปนู่นแล้ว

สำหรับผู้ที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้ที่ลิงก์นี้ครับ
https://drive.google.com/file/d/1FdsihIaT5yvtLBDFhi74yjfOwjoVPMvm/view?usp=sharing

ถ้าคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กเพจวิศวกรรีพอร์ต
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^