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

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

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
  • คลิกทำเครื่องหมายเป็นตารางวันที่ในกลุ่มปฏิทิน
  • คลิกทำเครื่องหมายเป็นตารางวันที่ในรายการแบบเลื่อนลง

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