เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
DAX Formula

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

time-intelligence

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

Time Intelligence คืออะไร?

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

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

ไฟล์ประกอบ

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

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

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

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

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

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

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

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

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

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

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

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

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 ได้เช่นเดิม โดยเลือกดังนี้

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

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

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

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

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

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

TotalRevenueLY =
CALCULATE ( [TotalRevenue], DATEADD ( dDate[Date], -1, YEAR ) )
Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function 8

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

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

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

This image has an empty alt attribute; its file name is Powerful-Data-Power-BI-1024x538.png
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 135
  •  
  •  
  •  
  •  
  • 135
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply