สูตร DAX สรุปออเดอร์ N เดือนย้อนหลัง [DAX Order Date Delivery Date ep.4]

“แล้วถ้าอยากรู้ออเดอร์ที่ส่งย้อนหลัง 6 เดือน ควรทำยังไงดีคะ?” น้องนิจิคนงามถามต่อ
คำถามนี้ทำผมอึ้ง เพราะมันไม่ง่ายเลย!

(บทความนี้คือ ep.4 อ่าน ep.1 ที่นี่, ep.2 ที่นี่, ep.3 ที่นี่)

รีพอร์ตที่น้องนิจิต้องการ หน้าตาแบบนี้ครับ

ถ้าคลิก slicer ปี 2020 และเดือน Oct ต้องแสดงข้อมูลการส่ง 6 เดือนย้อนหลัง นั่นคือเดือน May-20 ถึง Oct-20 และแสดงจำนวนออเดอร์ที่ยังไม่ส่ง ณ วันที่ 31 ตุลาคม 2020

แต่ถ้าคลิก slicer ปี 2020 และเดือน Mar ต้องแสดงข้อมูลการส่งออเดอร์ 6 เดือนย้อนหลัง นั่นคือเดือน Oct-19 ถึง Mar-20 และแสดงจำนวนออเดอร์ที่ยังไม่ส่ง ณ วันที่ 31 มีนาคม 2020

ไม่ง่าย ไม่ง่ายเลย..

จากข้อมูลตอนนี้ การจะทำรีพอร์ตแบบนี้ได้ ต้องเพิ่ม 4 ขั้นตอนคือ

สร้างตารางเลือกช่วงเวลา
สร้างตารางเลือกจำนวนเดือนย้อนหลัง
สร้าง measure คำนวณจำนวนออเดอร์
สร้าง measure แสดงผลเป็นชื่อตาราง

1. สร้างตารางเลือกช่วงเวลา

รีพอร์ตมี Slicer ให้เลือกปีและเดือน ซึ่งตอนนี้ยังไม่มีคอลัมน์ที่ทำแบบนั้นได้ ต้องสร้างตารางเพิ่ม

ทำไมใช้คอลัมน์ปีและเดือนในตาราง tOrderDate ไม่ได้?
เพราะคอลัมน์เดือน [OMonth] ในตาราง tOrderDate จะถูกใช้เป็น Rows ของรีพอร์ต
ถ้าใช้เป็น Slicer ข้อมูลจะถูกฟิลเตอร์เหลือเพียงเดือนเดียว แสดงผลย้อนหลัง N เดือน (เช่น 6 เดือน) ไม่ได้

ทำไมใช้คอลัมน์ปีและเดือนในตาราง tDeliveryDate ไม่ได้?
เพราะข้อมูลในตาราง tDeliveryDate มีค่าว่าง (Order ที่ยังไม่ส่ง) ถ้า Slicer มีค่าว่างจะดูแปลก ๆ

ทำไมใช้คอลัมน์เดือนและปีในตาราง tDMonthYear_wNotDelivered ไม่ได้?
เพราะคอลัมน์เดือน [DMonth] ในตาราง tDMonthYear_wNotDelivered จะถูกใช้เป็น Columns ของรีพอร์ต
ถ้าใช้เป็น Slicer ข้อมูลจะถูกฟิลเตอร์เหลือเพียงเดือนเดียว แสดงผลย้อนหลังไม่ได้

เมื่อไม่มีคอลัมน์ให้ใช้ จึงต้องสร้างตารางเพิ่ม โดยใช้ฟังก์ชัน SUMMARIZE
ในที่นี้ขอตั้งชื่อตารางว่า tSelectYearMonth
คลิกเลือก New table

ใช้สูตร

tSelectYearMonth = 
SUMMARIZE(
    tOrderDate,
    tOrderDate[OYear],
    tOrderDate[OMonthNum],
    tOrderDate[OMonth],
    tOrderDate[OMonthYear],
    tOrderDate[OYearMonthNum]
)

ได้ผลลัพธ์เป็นตารางหน้าตาแบบนี้

ปรับคอลัมน์ OMonth ให้เรียงตามคอลัมน์ OMonthNum (Sort by column)

ไม่ต้องสร้างความสัมพันธ์ระหว่างตารางนี้กับตารางใด ตารางนี้สร้างขึ้นเพื่อใช้เฉพาะกิจ ศัพท์เทคนิคเรียกว่า Disconnected Table

2. สร้างตารางเลือกจำนวนเดือนย้อนหลัง

สร้างตารางเพื่อใช้เป็น Slicer เลือกจำนวนเดือนย้อนหลัง เช่น 3, 6, 9, 12, 15

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

ในที่นี้ขอตั้งชื่อตารางว่า tRolling
คลิก New table

ใช้สูตร

tRolling = GENERATESERIES(3,15,3)

ได้ผลลัพธ์เป็นตารางที่มี 1 คอลัมน์แบบนี้

เพื่อให้เข้าใจง่าย ขอเปลี่ยนชื่อคอลัมน์จาก Value เป็น Rolling
(ดับเบิลคลิกที่หัวคอลัมน์แล้วเปลี่ยนชื่อได้เลย)

ไม่ต้องสร้างความสัมพันธ์ระหว่างตารางนี้กับตารางใด เพราะสร้างขึ้นเพื่อใช้เฉพาะกิจเช่นกัน (Disconnected Table)

3. สร้าง measure คำนวณจำนวนออเดอร์

การคำนวณจำนวนออเดอร์ที่ถูกส่งย้อนหลัง N เดือน (เช่น 3 เดือน, 6 เดือน) ของแต่ละช่วงเวลา เพื่อให้ง่ายควรสร้างตัวแปร 2 ตัว คือ

เดือนที่ต้องการเลือกดูรีพอร์ต (Report as of)
จำนวนเดือนย้อนหลัง (N Month)

สร้างตัวแปรโดยใช้สูตร DAX ดังนี้

VAR ReportAsOf = 
    SELECTEDVALUE(tSelectYearMonth[OYearMonthNum])

VAR NMonth = 
    SELECTEDVALUE(tRolling[Rolling],6)

นอกจากนี้ยังมีเดือนที่ระบุในแต่ละคอลัมน์ ซึ่งคอลัมน์นี้ต้องมีคำว่า “Not Delivered” จึงใช้เทคนิคสร้างตัวแปรเหมือนใน ep.3 (measure: Order_wNotDelivered) นั่นคือ

VAR DeliverMonthYear = 
    SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])

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

VAR Delivered =
    CALCULATE(
        COUNTROWS(tOrder),
        tDeliveryDate[DMonthYear]=DeliverMonthYear,
        tDeliveryDate[DYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]>ReportAsOf-NMonth
    )

แต่ถ้าเป็นตัวเลขที่แสดงในคอลัมน์ “Not Delivered” นั่นคือจำนวนออเดอร์ที่ยังไม่ส่ง ณ เวลานั้น ๆ (Report as of)
ใช้สูตร

VAR NotDelivered = 
    CALCULATE(
        COUNTROWS(tOrder),
        tDeliveryDate[DYearMonthNum]>ReportAsOf ||
            ISBLANK(tDeliveryDate[DYearMonthNum]),
        tOrderDate[OYearMonthNum]<=ReportAsOf           
    )

ตัวเลขที่แสดงเป็น Subtotal ใช้สูตร

VAR SubTotal =
    CALCULATE(
        COUNTROWS(tOrder),
        tOrderDate[OYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]>ReportAsOf-NMonth
    )

ประกอบตรรกะทั้งหมดเข้าด้วยกัน สร้าง Measure (ในที่นี้ตั้งชื่อว่า #Order_RollingNMonthAsOf)
คลิก New measure

ใช้สูตร DAX

#Order_RollingNMonthAsOf = 
VAR ReportAsOf = 
    SELECTEDVALUE(tSelectYearMonth[OYearMonthNum])
VAR NMonth = 
    SELECTEDVALUE(tRolling[Rolling],6)
VAR DeliverMonthYear = 
    SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
VAR Delivered =
    CALCULATE(
        COUNTROWS(tOrder),
        tDeliveryDate[DMonthYear]=DeliverMonthYear,
        tDeliveryDate[DYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]>ReportAsOf-NMonth
    )
VAR NotDelivered = 
    CALCULATE(
        COUNTROWS(tOrder),
        tDeliveryDate[DYearMonthNum]>ReportAsOf ||
            ISBLANK(tDeliveryDate[DYearMonthNum]),
        tOrderDate[OYearMonthNum]<=ReportAsOf           
    )
VAR SubTotal =
    CALCULATE(
        COUNTROWS(tOrder),
        tOrderDate[OYearMonthNum]<=ReportAsOf,
        tOrderDate[OYearMonthNum]>ReportAsOf-NMonth
    )
VAR Result =
    IF(
        HASONEVALUE(tDMonthYear_wNotDelivered[DMonthYear]),
        IF(
            DeliverMonthYear="Not Delivered",
            NotDelivered,
            Delivered
        ),
        SubTotal
    )
RETURN Result

ตรรกะหลักของการคำนวณคือ
เช็คว่าเป็นข้อมูลระดับเดือน (ไม่ใช่ระดับ Subtotal) ด้วยฟังก์ชัน HASONEVALUE
ถ้าเป็น “Not Delivered” ให้คำนวณแบบ NotDelivered
ถ้าไม่ใช่ (ไม่ใช่ “Not Delivered”) ให้คำนวณแบบ Delivered
ถ้าเป็น Subtotal ให้คำนวณแบบ SubTotal

VAR Result =
    IF(
        HASONEVALUE(tDMonthYear_wNotDelivered[DMonthYear]),
        IF(
            DeliverMonthYear="Not Delivered",
            NotDelivered,
            Delivered
        ),
        SubTotal
    )

ปรับ measure ให้แสดงผลแบบมีคอมมาคั่น

ใช้ Visual Matrix โดยปรับค่าดังนี้
Rows: OMonthYear จากตาราง tOrderDate
Columns: DMonthYear จากตาราง tDMonthYear_wNotDelivered
Values: #Order_Rolling6MAsOf

สร้าง Slicer 3 อัน โดยใช้
คอลัมน์ OYear จากตาราง tSelectYearMonth
คอลัมน์ OMonth จากตาราง tSelectYearMonth
คอลัมน์ Rolling จากตาราง tRolling

ทดลองคลิกเลือกข้อมูลแล้วจัดวางให้สวยงาม

4. สร้าง measure แสดงผลเป็นชื่อตาราง

รีพอร์ตนี้จะดูชัดเจนขึ้นถ้ามี ‘ชื่อตาราง’ แสดงว่าย้อนหลังกี่เดือน (เช่น 6 เดือน) และเป็นรีพอร์ต ณ เวลาใด (เช่น Mar-20)

‘ชื่อตาราง’ คือ Visual: Card นั่นเอง แต่ต้องสร้าง measure เพื่อแสดงผลใน Card

ในที่นี้ขอตั้งชื่อ measure ว่า RollingNMonthAsOf
คลิก New measure

ใช้สูตร DAX

RollingNMonthAsOf = 
"Rolling "&
SELECTEDVALUE(tRolling[Rolling])&
"M Delivery Report as of "&
SELECTEDVALUE(tSelectYearMonth[OMonthYear])

นำ measure ไปสร้าง Card

จัดวางเรียงให้สวยงาม เป็นอันเสร็จพิธี ^_^

“ว้าว! แบบนี้ใช่เลยค่ะ อยากทำได้มานานแล้ว” น้องนิจิดีใจมาก
“มีขั้นตอนนิดนึง แต่ไม่ยากใช่ไหมครับ”
“ถ้าบอกว่ายาก พี่จะโกรธไหมคะ..” น้องนิจิก้มหน้า นิ้วชี้สองข้างประกบเข้าหากัน
“ถ้าบอกว่าง่ายจะโกรธครับ เพราะผมใช้เวลาตั้งหลายวันกว่าจะคิดออก” ผมยิ้ม

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

เป็นเรื่องจริงที่สูตรสไตล์นี้ไม่ค่อยมีใครสอน ตอนผมคิดสูตรไม่ออก ค้นเท่าไรก็ไม่เจอ
พอคิดออกเลยบอกตัวเองว่า
“ถ้าไม่มีใครสอน งั้นสอนเองก็ได้ฟะ!”
จึงเป็นที่มาของบทความทั้ง 4 ep.

หวังว่าผู้อ่านทุกท่านคงได้ทั้งสาระและความบันเทิง ไม่มากก็มาก ๆ เลยนะครับ ^_^

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

บทความนี้แชร์ได้นะครับ ยิ่งมีคนอ่าน คนเขียนยิ่งมีกำลังใจครับ ^_^