Power BI ตอนที่ 22 : การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน

ในตัวอย่างที่ผ่านมาทั้งหมด มักจะเป็นเหตุการณ์ของวันนั้นๆ ที่เราสนใจเลย เช่น ถ้าเป็นการขายของ ก็จะสามารถวิเคราะห์ได้ว่า วันนั้นๆ มีการขายของอะไรไปเท่าไหร่บ้าง? แต่ถ้าหากเหตุการณ์ของเรามันเป็นช่วงเวลา ที่มีวันเริ่มต้นกับสิ้นสุดไม่ตรงกันล่ะ เราจะทำยังไงดี?

เช่น บอกเป็นช่วงเวลาของเรื่องเหล่านี้ว่าเริ่มวันไหนจบวันไหน

• การซื้อสินค้าที่มีเรื่องของวันสั่งซื้อกับวันส่งของที่ไม่ตรงกัน
• Campaign การตลาด
• ช่วงเวลาการซ่อมแซมเครื่องจักร
• สัญญา/กรมธรรม์

แบบนี้เราจะทำการวิเคราะห์ข้อมูลได้อย่างไร? คำถามนี้ผมเชื่อว่าหลายคนคงเจอกับการทำงานอยู่ และก็ไม่รู้จะทำยังไงกับมันดี ดังนั้นผมเลยเขียนบทความนี้ขึ้นมาช่วยแก้ไขปัญหาให้ครับ

แนวทางแก้ปัญหา และ ไฟล์ประกอบ

โชคดีที่ท่านอาจารย์ Alberto Ferrari แห่ง SQLBI ได้เขียนบทความแนวนี้ไว้แล้วด้วย ซึ่งเป็นทางแก้ที่น่าสนใจ ดังนั้นผมก็จะขออธิบายโดยใช้แนวทางของเค้า และเพื่อให้ง่ายกับทุกคน ผมก็จะใช้ไฟล์ประกอบของเค้าด้วยครับ (ซึ่งเป็นเรื่องของวันสั่งซื้อของกับวันส่งของ) แต่ผมก็จะมีการอธิบายเสริมด้วยความรู้ที่ผมมี เผื่อเพื่อนๆ จะเข้าใจมากขึ้นนะครับ

ถ้าผูก Relationship แบบทื่อๆ

หากเราใช้การผูก Relationship ตามปกติ เช่น ผูกแค่วันเริ่ม Event แบบปกติ

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

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

ผูก Relationship 2 เส้น

เราต้องผูก Relationship ของตาราง Date เข้ากับ Orders ให้มีทั้งวันเริ่มวันจบ แปลว่าต้องมี Relationship 2 เส้น นั่นเอง แต่ Power BI จะยอมให้มี Relationship ที่ Active ได้แค่เส้นเดียวเท่านั้น ซึ่งก็ไม่เป็นไร เราจะให้มัน Active ที่วันเริ่มต้นคือ OrderDateKey ไว้ (เป็นเส้นทึบ) ส่วนวันสิ้นสุด ShipDateKey ปล่อยให้ inactive (เป็นเส้นประ) ไปซะ

ทีนี้พอเราสร้าง Relationship แล้ว เราจะสามารถเขียนสูตรใน Measure โดยใช้ฟังก์ชัน USERELATIONSHIP เพื่อเลือกใช้ได้ว่าจะใช้ Relationship ตัวไหน Active หรือไม่ Active ตอนไหนก็ได้

เช่น ถ้าเราลาก Amount หรือ SUM Amount เฉยๆ มันจะยึดตามวันที่ OrderDate เพราะว่า Relationship Active อยู่ แปลว่า Amount ที่คำนวณได้ก็คือ OrderAmount นั่นเอง ดังนั้นเราจะเขียนสูตร DAX ง่ายๆ ได้ว่า

OrderAmount = SUM(Orders[Amount])

แต่ถ้าเราอยากจะให้มันคำนวณ ShipAmount เราก็ต้องใช้ USERELATIONSHIP มาช่วย ซึ่งเราจะใช้ในฟังก์ชัน CALCULATE นะครับ

ShipAmount =
CALCULATE (
    [OrderAmount],
    USERELATIONSHIP ( Orders[ShipDateKey], 'Date'[DateKey] )
)

ซึ่งพอลาก Measure ทั้งสองตัวลงมาใน Visual คู่กับวันที่ก็จะได้แบบนี้

ถึงตรงนี้ยังไม่มีอะไรพิสดารมากนัก แค่ทำความรู้จักเจ้า USERELATIONSHIP นิดหน่อยเท่านั้น ซึ่งถ้าอยากนับจำนวน Order ก็สามารถใช้ COUNTROWS แทนการ SUM ได้ เช่น

จะนับจำนวน Order ตาม OrderDate

OrderTX=COUNTROWS(Orders)

นับจำนวน Order ตาม ShippingDate

ShipTX =
CALCULATE (
    [OrderTX],
    USERELATIONSHIP ( Orders[ShipDateKey], 'Date'[DateKey] )
)

ลากลง Visual ก็จะได้แบบนี้

การคำนวณ ActiveOrder แก้ไขประเด็นช่วงเวลา

คราวนี้มาถึงปัญหาของเราจริงๆ ละ ว่าถ้าเราอยากรู้ว่า แต่ละมัน มีจำนวน Order หรือ Amount ของ Order ที่ Active อยู่เท่าไหร่? นั่นคือ มีการ Order มาแล้ว แต่ยังไม่ได้ Ship (แม้ไม่ได้เริ่มต้นหรือสิ้นสุดวันนั้น)

เช่น ถ้าดู ActiveOrder ที่ 8 Jan (สิ้นวัน) แปลว่า

• ต้องเป็น Order ทั้งหมดที่ Order มาก่อนหน้านั้น คือ OrderDate <=8Jan
• และต้องเป็น Order ที่ Ship ตั้งแต่ 8 Jan ด้วย นั่นคือ ShipDate >= 8Jan ด้วย (มองว่าถ้า Ship วันที่ 8 พอดีถือว่า Active อยู่)

ปัญหา คือ ถ้าเราเขียนสูตรตามปกติ เราจะไม่เห็นข้อมูลของวันที่อยู่ระหว่างกลางเลย เช่น ถ้าสมมติ EventA เริ่ม 1 Jan แล้วจบ 10 Jan หากเราทำการ Filter Date[Date] ให้เป็นวันที่ 8 Jan เจ้า Power BI ก็จะใช้ Relationship เพื่อ Filter ข้อมูลไหลจาก Date[Date] ไป Order แล้วจะพบว่าไม่มี Row ของ Event A อยู่ ทั้งๆ ที่จริงๆ มันก็ยังคงเป็น Event ที่กำลังเกิดขึ้นอยู่แท้ๆ

นอกจากนี้ เรายังต้องมาตกลงนิยามกันด้วยว่า ถ้าใน Visual ต้องแสดงข้อมูลเป็นระดับที่ใหญ่กว่าวัน เช่น Week Month Quarter หรือ Year อันนี้เราต้องมานิยามดีๆ แล้วว่าจะต้องการแสดงแบบไหน เช่น ถ้าดูทั้งเดือน Jan แปลว่า จะให้แสดง ActiveOrder ณ วันสุดท้ายของเดือน Jan หรือว่าจะเอา ActiveOrder ในทุกวันของเดือน Jan มาเฉลี่ยกัน เป็นต้น (ถ้าเป็นระดับวัน มันไม่มีปัญหาเรื่องการตกลงนิยาม)

ทางแก้ไขมีอยู่ 2 แนวทาง

• แนวทางแรกเน้นใช้สูตร DAX คำนวณขณะที่กดรายงานเล่น
• แนวทางที่สองคือสร้างตารางไว้ใน Data Model ก่อนเลย เพื่อให้กดรายงานได้เร็วมากขึ้น แต่ข้อเสียคือไฟล์จะใหญ่ขึ้น

ซึ่งในบทความนี้เราจะมาลองดูแนวทางแรกกันก่อนครับ เพราะง่ายกว่าพอสมควร และก็พอใช้งานได้ ไฟล์ไม่ใหญ่ด้วย

ใช้สูตร DAX คำนวณขณะที่กดรายงาน

ในบทความของคุณ Alberto ใช้การเฉลี่ย (Average) เพื่อแสดง ActiveOrder ในระดับที่ใหญ่กว่า ดังนั้นเพื่อไม่ให้เหมือนกัน ของผมจะเอา ActiveOrder ในวันสุดท้ายของเดือนมาแสดงแล้วกัน 555

ดังนั้น ผมจะเขียน Measure ที่เรียกว่า ActiveTX ขึ้นมา โดยใช้การ FILTER ตามช่วงเวลามาช่วย หลักการ คือ

• ผมมีการใช้ VAR มาดึงค่าวันที่ล่าสุดที่มองเห็น ณ Filter Context นั้นๆ ไว้ใน LastVisibleDate
• ทีนี้เราอยากได้ Order ที่ยัง Active อยู่ ซึ่งแปลว่า มีการ Order มาแล้ว แต่ยังไม่ได้ Ship
• จากนั้นใช้ FILTER สร้างตารางวันที่ขึ้นมา 2 อัน
  • อันแรกเป็นชุดวันที่ OrderDate ทั้งหมดที่ไม่เกินวัน LastVisibleDate
  • อันสองเป็นชุดวันที่ ShipDate ทั้งหมดตั้งแต่ LastVisibleDate เป็นต้นไป
• ใช้ CALCULATE ปลด Filter ออกจากตาราง Date ทั้งหมดด้วย ALL แล้ว Filter เข้าไปใหม่ด้วยวันที่ตามที่สร้างไว้ทั้งสองชุด
  • แปลว่าเงื่อนไขในการ Filter ที่ได้คือ จะเหลือเฉพาะบรรทัดที่ตรงกับทั้ง 2 เงื่อนไขเท่านั้น คือ Orders[OrderDateKey] <= LastVisibleDate และ Orders[ShipDateKey] >= LastVisibleDate

ActiveTX = 
    VAR LastVisibleDate = MAX('Date'[DateKey])
    VAR OrderedBeforeCurrentDate =
        FILTER (
            ALL ( Orders[OrderDateKey] ),
            Orders[OrderDateKey] <= LastVisibleDate 
        )
    VAR ShippedAfterCurrentDate =
        FILTER (
            ALL ( Orders[ShipDateKey] ),
            Orders[ShipDateKey] >= LastVisibleDate 
        )
    RETURN
        CALCULATE (
            [OrderTX],
            ALL ( 'Date' ),
            OrderedBeforeCurrentDate,
            ShippedAfterCurrentDate
            
        )

ซึ่งพอใส่ลงไปใน Visual จะได้ดังนี้ (ถ้าสังเกตจะเห็นว่ากว่าตัวเลขจะขึ้นมา มันมี Delay พอสมควร เพราะการคำนวณหนักๆ มันเกิดขึ้นตอนที่สร้าง visual ตามแนวทางแรกนั่นเอง)

เอาเข้าจริงถ้าจะคำนวณโดยใช้ CALCULATE กับ FILTER แบบนี้เราไม่ต้องสร้าง Relationship 2 เส้นก็ยังทำงานได้ เช่น ถ้าจะใช้ Relationship เส้นเดียว ไปที่ OrderDate ผมก็ยังสามารถเขียนสูตร ShipTX2 เป็นดังนี้ได้ และมันยังทำงานได้ค่าถูกต้องด้วย

ShipTX2 = 
SUMX (
    VALUES ( 'Date'[DateKey] ),
    VAR CurrentDate = 'Date'[DateKey]
    VAR ShippedatCurrentDate =
        FILTER (
            ALL ( Orders[ShipDateKey] ),
            Orders[ShipDateKey] = CurrentDate 
        )
    RETURN
        CALCULATE (
            COUNTROWS ( Orders ),
            ALL ( 'Date' ),
            ShippedatCurrentDate
        )
)

จะเห็นว่า แม้มีเส้น Relationship เดียวก็ทำงานได้ แต่ทว่า การคำนวณจะค่อนข้างช้ากว่าการมี Relationship จริงๆ นิดหน่อยนะครับ

สรุป

ดังนั้นถ้าเลือกได้ ควรจะมีเส้น Relationship จริงๆ แล้วใช้ USERELATIONSHIP จะดีกว่านะครับ อย่าไปใช้การคำนวณ DAX ซับซ้อนโดยไม่จำเป็นเลย การใช้ CALCULATE กับ FILTER นั้นใช้กรณีที่ใช้ Relationship ปกติทำไม่ได้ เช่น ActiveOrder จะดีกว่าครับ

ซึ่งเดี๋ยวบทความถัดไป ผมจะพาไปสำรวจแนวทางที่ 2 ที่สร้าง Table ขึ้นมาจริงๆใน Data Model ซึ่งจะช่วยให้ตอนกดรายงานทำงานเร็วขึ้นมาก แต่ต้องแลกมาด้วยขนาด Data Model ที่ใหญ่ขึ้นครับ

สารบัญ 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 ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่