การทำความเข้าใจตารางข้อมูล

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

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

คุณสามารถนำเข้าตารางวันที่พร้อมกับข้อมูลอื่น ๆ จากแหล่งข้อมูลหรือคุณสามารถสร้างตารางวันที่ด้วยตัวเองในแบบจำลองข้อมูล

ในบทนี้คุณจะเข้าใจแง่มุมต่างๆของตารางวันที่ หากคุณคุ้นเคยกับตารางวันที่ในโมเดลข้อมูล Power Pivot คุณสามารถข้ามบทนี้และดำเนินการกับบทต่อ ๆ ไปได้ มิฉะนั้นคุณสามารถเข้าใจตารางวันที่ในโมเดลข้อมูล Power Pivot

ตารางวันที่คืออะไร?

ตารางวันที่คือตารางในแบบจำลองข้อมูลโดยมีวันที่ที่ต่อเนื่องกันอย่างน้อยหนึ่งคอลัมน์ในช่วงเวลาที่กำหนด สามารถมีคอลัมน์เพิ่มเติมแทนช่วงเวลาต่างๆ อย่างไรก็ตามสิ่งที่จำเป็นคือคอลัมน์ของวันที่ที่ต่อเนื่องกันตามที่ฟังก์ชัน DAX Time Intelligence ต้องการ

ตัวอย่างเช่น,

• ตารางวันที่สามารถมีคอลัมน์เช่นวันที่เดือนบัญชีไตรมาสบัญชีและปีบัญชี

• ตารางวันที่สามารถมีคอลัมน์เช่นวันที่เดือนไตรมาสและปี

ตารางวันที่ที่มีวันที่ติดกัน

สมมติว่าคุณต้องทำการคำนวณในช่วงของปีปฏิทิน จากนั้นตารางวันที่ต้องมีคอลัมน์อย่างน้อยหนึ่งคอลัมน์ที่มีชุดวันที่ที่ต่อเนื่องกันรวมทั้งวันที่ทั้งหมดในปีปฏิทินนั้น ๆ

ตัวอย่างเช่นสมมติว่าข้อมูลที่คุณต้องการที่จะดูมีจากวันที่ 1 เมษายน 2014 ผ่านพฤศจิกายน 30 ^TH 2016

• หากคุณมีการรายงานเกี่ยวกับปฏิทินปีคุณต้องตารางวันกับคอลัมน์ - วันซึ่งมีทุกวันตั้งแต่เดือนมกราคม 1 ^{เซนต์} , 2014 31 ธันวาคม^ST 2016 ในลำดับ

• หากคุณมีการรายงานเกี่ยวกับปีงบประมาณและสิ้นปีงบประมาณของคุณคือ 30 ^THมิถุนายนที่คุณต้องการตารางวันกับคอลัมน์ - วันซึ่งมีทุกวันตั้งแต่เดือนกรกฎาคม 1 ^ST 2013 ถึงเดือนมิถุนายน 30 ^TH 2017 ใน ลำดับ.

• หากคุณต้องรายงานทั้งในปฏิทินและปีบัญชีคุณสามารถมีตารางวันที่เดียวที่ครอบคลุมช่วงวันที่ที่ต้องการ

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

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

ตารางวันที่ดูเหมือนภาพหน้าจอต่อไปนี้

การเพิ่มตารางวันที่ในโมเดลข้อมูล

คุณสามารถเพิ่มตารางวันที่ลงในโมเดลข้อมูลได้ด้วยวิธีใดวิธีหนึ่งดังต่อไปนี้ -

• การนำเข้าจากฐานข้อมูลเชิงสัมพันธ์หรือแหล่งข้อมูลอื่น ๆ

• การสร้างตารางวันที่ใน Excel จากนั้นคัดลอกหรือลิงก์ไปยังตารางใหม่ใน Power Pivot

• การนำเข้าจาก Microsoft Azure Marketplace

การสร้างตารางวันที่ใน Excel และคัดลอกไปยังตัวแบบข้อมูล

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

• เปิดแผ่นงานใหม่ใน Excel

• ประเภท - วันที่ในแถวแรกของคอลัมน์

• พิมพ์วันที่แรกในช่วงวันที่ที่คุณต้องการสร้างในแถวที่สองในคอลัมน์เดียวกัน

• เลือกเซลล์คลิกจุดจับเติมแล้วลากลงเพื่อสร้างคอลัมน์ของวันที่ที่ติดกันในช่วงวันที่ที่ต้องการ

ตัวอย่างเช่นพิมพ์ 1/1/2014 คลิกที่จับเติมแล้วลากลงเพื่อเติมวันที่ที่ต่อเนื่องกันจนถึงวันที่ 31/12/2016

• คลิกคอลัมน์วันที่
• คลิกแท็บ INSERT บน Ribbon
• คลิกตาราง
• ตรวจสอบช่วงของตาราง
• คลิกตกลง

ตารางของคอลัมน์วันเดียวพร้อมแล้วใน Excel

• เลือกตาราง
• คลิกคัดลอกบน Ribbon
• คลิกหน้าต่าง Power Pivot
• คลิกวางบน Ribbon

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

วางกล่องโต้ตอบแสดงตัวอย่างดังที่แสดงในภาพหน้าจอต่อไปนี้

• พิมพ์วันที่ในกล่องชื่อตาราง
• ดูตัวอย่างข้อมูล

• เลือกช่อง - ใช้แถวแรกเป็นส่วนหัวของคอลัมน์
• คลิกตกลง

ซึ่งจะคัดลอกเนื้อหาของคลิปบอร์ดไปยังตารางใหม่ในโมเดลข้อมูล

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

การเพิ่มคอลัมน์วันที่ใหม่ในตารางวันที่

จากนั้นคุณสามารถเพิ่มคอลัมน์จากการคำนวณลงในตารางวันที่ตามความต้องการสำหรับการคำนวณของคุณ

ตัวอย่างเช่นคุณสามารถเพิ่มคอลัมน์ - วันเดือนปีและไตรมาสดังต่อไปนี้ -

• Day

  =DAY('Date'[Date])

• Month

  =MONTH('Date'[Date])

• Year

  =YEAR('Date'[Date])

• ไตรมาส

  = CONCATENATE ("QTR", INT (('วันที่' [เดือน] +2) / 3))

ตารางวันที่ที่เป็นผลลัพธ์ในแบบจำลองข้อมูลดูเหมือนภาพหน้าจอต่อไปนี้

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

การสร้างตารางวันที่สำหรับปีปฏิทิน

โดยทั่วไปปีปฏิทินจะรวมวันที่ 1 มกราคมถึง 31 ธันวาคมของปีและยังรวมถึงวันหยุดที่ทำเครื่องหมายไว้สำหรับปีนั้น ๆ ด้วย เมื่อคุณทำการคำนวณคุณอาจต้องพิจารณาเฉพาะวันทำการเท่านั้นไม่รวมวันหยุดสุดสัปดาห์และวันหยุด

สมมติว่าคุณต้องการสร้างตารางวันที่สำหรับปีปฏิทิน 2017

• สร้างตาราง Excel กับวันที่คอลัมน์ประกอบด้วยวันต่อเนื่องกันตั้งแต่วันที่ 1 ^{เซนต์}เดือนมกราคม 2017-31 ^{เซนต์}ธันวาคม 2017 (โปรดดูที่ส่วนก่อนหน้านี้ทราบวิธีการทำเช่นนี้.)

• คัดลอกตาราง Excel และวางลงในตารางใหม่ในแบบจำลองข้อมูล (ดูส่วนก่อนหน้านี้เพื่อทราบวิธีการดำเนินการนี้)

• ตั้งชื่อตารางว่าปฏิทิน

• เพิ่มคอลัมน์จากการคำนวณต่อไปนี้ -

  • วัน = DAY ('ปฏิทิน' [วันที่])

  • เดือน = MONTH ('ปฏิทิน' [วันที่])

  • ปี = YEAR ('ปฏิทิน' [วันที่])

  • วันในสัปดาห์ = FORMAT ('ปฏิทิน' [วันที่], "DDD")

  • ชื่อเดือน = FORMAT ('ปฏิทิน' [วันที่], "MMM")

การเพิ่มวันหยุดลงในตารางปฏิทิน

เพิ่มวันหยุดลงในตารางปฏิทินดังนี้ -

• รับรายชื่อวันหยุดประจำปีที่ประกาศไว้

• ตัวอย่างเช่นสำหรับสหรัฐอเมริกาคุณสามารถดูรายการวันหยุดของปีใดก็ได้ที่ต้องการจากลิงค์ต่อไปนี้ http://www.calendar-365.com/.

• คัดลอกและวางลงในแผ่นงาน Excel

• คัดลอกตาราง Excel และวางลงในตารางใหม่ในแบบจำลองข้อมูล

• ตั้งชื่อตารางเป็นวันหยุด

• ถัดไปคุณสามารถเพิ่มคอลัมน์จากการคำนวณของวันหยุดลงในตารางปฏิทินโดยใช้ฟังก์ชัน DAX LOOKUPVALUE

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

ฟังก์ชัน DAX LOOKUPVALUE จะค้นหาพารามิเตอร์ที่สามนั่นคือปฏิทิน [วันที่] ในพารามิเตอร์ที่สองคือวันหยุด [วันที่] และส่งกลับพารามิเตอร์แรกคือวันหยุด [วันหยุด] หากมีการจับคู่ ผลลัพธ์จะมีลักษณะเหมือนที่แสดงในภาพหน้าจอต่อไปนี้

การเพิ่มคอลัมน์ในปีงบประมาณ

ปีงบประมาณมักจะมีวันที่จาก 1 ^{เซนต์}ของเดือนหลังจากที่สิ้นปีงบประมาณที่จะสิ้นสุดปีงบประมาณถัดไป ตัวอย่างเช่นถ้าสิ้นปีงบประมาณ 31 ^{เซนต์}มีนาคมแล้วปีงบประมาณช่วงตั้งแต่วันที่ 1 ^{เซนต์}เดือนเมษายนถึงวันที่ 31 ^{เซนต์}มีนาคม

คุณสามารถรวมรอบระยะเวลาทางการเงินในตารางปฏิทินโดยใช้สูตร DAX -

• เพิ่มการวัดสำหรับปีงบประมาณ

  FYE:=3

• เพิ่มคอลัมน์จากการคำนวณต่อไปนี้ -

  • Fiscal Year

    = IF ('ปฏิทิน' [เดือน] <= 'ปฏิทิน' [FYE], 'ปฏิทิน' [ปี], 'ปฏิทิน' [ปี] +1)

  • Fiscal Month

    = IF ('ปฏิทิน' [เดือน] <= 'ปฏิทิน' [FYE], 12-'ปฏิทิน' [FYE] + 'ปฏิทิน' [เดือน], 'ปฏิทิน' [เดือน] - 'ปฏิทิน' [FYE])

  • Fiscal Quarter

    = INT (('ปฏิทิน' [เดือนบัญชี] +2) / 3)

การตั้งค่าคุณสมบัติตารางวันที่

เมื่อคุณใช้ฟังก์ชัน DAX Time Intelligence เช่น TOTALYTD, PREVIOUSMONTH และ DATESBETWEEN ฟังก์ชันเหล่านี้ต้องการข้อมูลเมตาเพื่อให้ทำงานได้อย่างถูกต้อง Date Table Property ตั้งค่าข้อมูลเมตาดังกล่าว

การตั้งค่าคุณสมบัติตารางวัน -

• เลือกตารางปฏิทินในหน้าต่าง Power Pivot
• คลิกแท็บออกแบบบน Ribbon
• คลิกทำเครื่องหมายเป็นตารางวันที่ในกลุ่มปฏิทิน
• คลิกทำเครื่องหมายเป็นตารางวันที่ในรายการแบบเลื่อนลง

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