Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function

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

Time Intelligence คืออะไร?

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

เรามาดูตัวอย่างการใช้ฟังก์ชันกลุ่ม Time Intelligence กันดีกว่าว่ามันทำอะไรได้บ้าง และใช้งานยังไง?

ไฟล์ประกอบ

ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

การคำนวณยอดขายสะสมตั้งแต่ต้นปี (Year-To-Date : YTD)

ก่อนอื่นให้เราสร้าง Visual แบบ Table ใส่ ปี เดือน และ Total Revenue ลงไป (ที่เลือก ปี กับ เดือนมา เพื่อให้เห็นภาพชัดๆ)

ถ้าเราอยากได้ยอดขายสะสม วิธีที่ง่ายที่สุดคือใช้ Quick Measure โดยคลิ๊กขวาปุ่ม New Quick Measure ได้เลย

จากนั้นกด ok ด้านล่าง (ถ้ากดปุ่มไม่ได้เพราะอยู่ล่างเกินไป ให้ย้าย task bar ของ windows ไปด้านขวานะ…)

เราจะได้ measure ชื่อ TotalRevenue YTD ออกมาที่ซัก Table นึง (กด Search หา Field ชื่อ YTD ง่ายสุด)

จากนั้นก็ลากลงมาใน Visual ซะเราก็จะได้ยอด Total Revenue แบบ Year-To-Date

และก็ได้สูตรมาว่า

TotalRevenue YTD = TOTALYTD([TotalRevenue], 'dDate'[Date])

ซึ่งเจ้า TOTALYTD นี่คือฟังก์ชันสำเร็จรูปที่สามารถเปลี่ยน Filter Context ให้กับ Measure โดยให้มี Filter ช่วงวันที่นับตั้งแต่ต้นปีจนถึงวันล่าสุดที่ถูก Filter ในแต่ละบรรทัดนั้นๆ

• เช่น เดิม ปี 2011 เดือน 5 มี Filter Context เรื่อง Date ตั้งแต่วันที่ 1/5/2011 – 31/5/2011
• พอใช้ TOTALYTD มันจะเปลี่ยน Filter Context ไปเป็น 1/1/2011-31/5/2011 ทันที (กลายเป็นสะสม 5 เดือนนั่นเอง)

และแน่นอนว่ามันก็มี TOTALQTD (Quarter to Date) และ TOTALMTD (Month to Date) ด้วย ลองไปใช้ดูได้

ซึ่งจริงๆ แล้ว TOTALYTD นั้น เหมือนกับเอา CALCULATE + DATESYTD ดังนี้

TotalRevenue YTD = 
TOTALYTD([TotalRevenue], 'dDate'[Date])

TotalRevenue YTD2 = 
CALCULATE([TotalRevenue],DATESYTD(dDate[Date]))

เนื่องจากฟังก์ชัน DATESYTD มีความสามารถในการ Filter เป็นช่วงเวลานับตั้งแต่ต้นปีนั่นเอง

ทีนี้หลายคนคงสงสัยว่าแล้วจะเรียนรู้การเขียนด้วย CALCULATE ไปทำไม ทั้งๆที่เขียน TOTALYTD ก็ง่ายกว่าตั้งเยอะ ?

นั่นเป็นเพราะหากใช้ CALCULATE เราสามารถใส่ Filter ได้เรื่อยๆ แต่ TOTALYTD ใส่ Filter ได้ 1 ตัวเท่านั้น ตามวิธีการใช้งานคือ

TOTALYTD ( <Expression>, <Dates> , [<Filter>] , [<YearEndDate>] )

แต่ถ้าไม่ได้จะ Filter อะไรเพิ่มเติม ใช้ TOTALYTD ก็ง่ายกว่าจริงๆ นั่นแหละ

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

TotalRevenue YTD2 = CALCULATE([TotalRevenue],DATESYTD(dDate[Date]))

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

TotalRevenue YTD3 = 
VAR currentLastdate = LASTDATE ( dDate[Date] )
VAR currentYear =  YEAR ( currentLastdate )
VAR firstdayofYear =  DATE ( currentYear, 1, 1 )
RETURN
CALCULATE ( [TotalRevenue],
        DATESBETWEEN ( dDate[Date], firstdayofYear, currentLastdate )
        )

และถ้าจะไม่ใช้ DATESBETWEEN อีก เราก็อาจจะต้องใช้ FILTER กับ ALL ร่วมกันดังนี้

TotalRevenue YTD4 = 
VAR currentLastdate = LASTDATE ( dDate[Date] )
VAR currentYear =  YEAR ( currentLastdate )
VAR firstdayofYear =  DATE ( currentYear, 1, 1 )
RETURN
CALCULATE ( [TotalRevenue],
        FILTER(ALL(dDate[Date]),dDate[Date]>=firstdayofYear && dDate[Date]<=currentLastdate)
        )

เทียบหลายๆ วิธี

แปลว่า 3 ตัวนี้ก็มีค่าเท่ากันทุกประการนั่นเอง

• DATESYTD(dDate[Date])
• DATESBETWEEN ( dDate[Date], firstdayofYear, currentLastdate )
• FILTER(ALL(dDate[Date]),dDate[Date]>=firstdayofYear && dDate[Date]<=currentLastdate)

เอาล่ะ สำหรับการทำความเข้าใจฟังก์ชันพวก YTD ขอจบเท่านี้ก่อน ลองมาดูอีกกลุ่มนึงคือ การเทียบกับยอดในอีกช่วงเวลาบ้าง

การคำนวณยอดขายเทียบกับปีก่อน

เราสามารถใช้ Quick Measure ได้เช่นเดิม โดยเลือกดังนี้

มันจะออกมาเป็น

TotalRevenue YoY% =
VAR __PREV_YEAR =
    CALCULATE ( [TotalRevenue], DATEADD ( 'dDate'[Date], -1, YEAR ) )
RETURN
    DIVIDE ( [TotalRevenue] - __PREV_YEAR, __PREV_YEAR )

ซึ่งจะเป็น % การเปลี่ยนแปลง เทียบกับปีก่อนหน้า ดังนี้

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

TotalRevenueLY =
CALCULATE ( [TotalRevenue], DATEADD ( dDate[Date], -1, YEAR ) )

ซึ่งเจ้า DATEADD มีความสามารถในการเลื่อนวันออกไปตามช่วงเวลาที่ต้องการ ตามหน่วยที่กำหนด ซึ่งเลื่อนได้ทั้งหน่วย DAY MONTH QUARTER YEAR เลยล่ะ เลื่อนย้อนเวลาก็ติดลบ เลื่อนไปข้างหน้าก็เป็นเลขบวก

จะว่าไปมันก็ดูคล้ายๆ EDATE ใน Excel เลย แต่ EDATE เลื่อนได้ในหน่วยเดือนเท่านั้น และ DATEADD ให้ผลเป็น Table ซึ่งใช้ใน Filter ของ CALCULATE ได้ แต่ว่า EDATE ให้ผลเป็นค่าตัวเลข (ดังนั้นเราจึงไม่สามารถใช้ EDATE แบบ -12 เดือนในเคสนี้นะ)

ทีนี้ถ้าเราจะเลื่อนย้อน 1 ปี เพื่อเทียบกับยอดในปีก่อน เราสามารถใช้ฟังก์ชันสำเร็จรูปชื่อ SAMEPERIODLASTYEAR ได้เลย แบบนี้

TotalRevenueLY2 = CALCULATE([TotalRevenue], SAMEPERIODLASTYEAR(dDate[Date]))

และถ้าเราจะเขียนแบบ Manual เอง แบบไม่พึ่งพา Time Intelligence มันก็จะประมาณนี้

TotalRevenueLY3 = 
VAR currentLastdate = LASTDATE (dDate[Date])
VAR currentFirstdate = FIRSTDATE(dDate[Date])
VAR LYLastdate= EDATE(currentLastdate,-12)
VAR LYFirstdate= EDATE(currentFirstdate,-12)
RETURN
CALCULATE ( [TotalRevenue],
        FILTER(ALL(dDate[Date]),dDate[Date]>=LYFirstdate && dDate[Date]<=LYLastdate)
        )

เทียบหลายๆ วิธี

แปลว่า 3 ตัวนี้ก็มีค่าเท่ากันทุกประการนั่นเอง

• SAMEPERIODLASTYEAR(dDate[Date])
• DATEADD(dDate[Date], -1, YEAR)
• FILTER(ALL(dDate[Date]),dDate[Date]>=LYFirstdate && dDate[Date]<=LYLastdate)

ซึ่งแน่นอนว่า SAMEPERIODLASTYEAR นั้นมีความ Flexible น้อยที่สุด แต่ก็เขียนง่ายสุดด้วยนั่นเอง

สารบัญ 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

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี