ทำความเข้าใจและสร้างตารางวันที่ใน Power Pivot ใน Excel

ตารางวันที่ใน Power Pivot เป็นสิ่งจําเป็นสําหรับการเรียกดูและการคํานวณข้อมูลเมื่อเวลาผ่านไป บทความนี้จะให้ความเข้าใจอย่างถ่องแท้เกี่ยวกับตารางวันที่ และวิธีการสร้างตารางวันที่ใน Power Pivot บทความนี้อธิบายโดยเฉพาะอย่างยิ่ง:

• เพราะเหตุใดตารางวันที่จึงมีความสําคัญต่อการเรียกดูและการคํานวณข้อมูลตามวันที่และเวลา
• วิธีใช้ Power Pivot เพื่อเพิ่มตารางวันที่ลงในตัวแบบข้อมูล
• วิธีการสร้างคอลัมน์วันที่ใหม่ เช่น ปี เดือน และช่วงเวลาในตารางวันที่
• วิธีการสร้างความสัมพันธ์ระหว่างตารางวันที่และตารางข้อเท็จจริง
• วิธีการทํางานกับเวลา

บทความนี้มีไว้สําหรับผู้ใช้ที่เพิ่งเริ่มใช้ Power Pivot อย่างไรก็ตาม สิ่งสําคัญคือต้องมีความเข้าใจที่ดีเกี่ยวกับการนําเข้าข้อมูล การสร้างความสัมพันธ์ และสร้างคอลัมน์และการวัดจากการคํานวณ

บทความนี้ ไม่ได้ อธิบายวิธีการใช้ฟังก์ชัน DAX Time-Intelligence ในสูตรการวัด สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการสร้างหน่วยวัดด้วยฟังก์ชันตัวแสดงเวลาของ DAX ให้ดูที่ ตัวแสดงเวลาใน Power Pivot ใน Excel

เนื้อหา

• ทําความเข้าใจตารางวันที่

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

  • นําเข้าจากฐานข้อมูลเชิงสัมพันธ์

  • สร้างตารางวันที่ใน Excel

    • วิธีการ: สร้างตารางวันที่ใน Excel และคัดลอกลงในตัวแบบข้อมูล

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

  • ฟังก์ชันวันที่และเวลาของ DAX

  • ตัวอย่างสูตรสําหรับปีปฏิทิน

    • ปี
    • เดือน
    • ไตรมาส
    • ชื่อเดือน

  • ตัวอย่างสูตรสําหรับปีงบประมาณ

    • ปีงบประมาณ
    • เดือนงบประมาณ
    • ไตรมาสงบประมาณ

• วันหยุดหรือวันที่พิเศษ

• ปฏิทินแบบกําหนดเอง - สิบสามช่วงสี่สัปดาห์

  • สัปดาห์
  • เครื่องหมายมหัพภาค
  • ช่วงเวลา ปีงบประมาณ
  • ระยะเวลาในปีงบประมาณ

• ความสัมพันธ์

  • หลายความสัมพันธ์
  • ความสัมพันธ์ที่ไม่ได้ใช้งาน
  • หลายตารางวันที่

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

• ทํางานกับเวลา

• การทําให้วันที่มีประโยชน์มากขึ้น

• ภาคผนวก

  • การแปลงวันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูล Date
  • แหล่งข้อมูลเพิ่มเติม

ทําความเข้าใจตารางวันที่

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

รายงาน Power View

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

รายการเขตข้อมูลของ Power View

สําหรับคอลัมน์วันที่อย่างปี เดือน และไตรมาส เพื่อรวมวันที่ทั้งหมดไว้ภายในช่วงที่เกี่ยวข้อง ตารางวันที่ ต้องมี อย่างน้อยหนึ่งคอลัมน์ที่มีชุดของวันที่ต่อเนื่องกัน กล่าวคือ คอลัมน์นั้นต้องมีหนึ่งแถวสําหรับทุกวันของแต่ละปีที่รวมอยู่ในตารางวันที่

ตัวอย่างเช่น ถ้าข้อมูลที่คุณต้องการเรียกดูมีวันที่ตั้งแต่วันที่ 1 กุมภาพันธ์ 2553 จนถึงวันที่ 30 พฤศจิกายน 2555 และคุณรายงานเป็นปีปฏิทิน คุณจะต้องใช้ตารางวันที่ที่มีช่วงวันที่ตั้งแต่วันที่ 1 มกราคม 2553 จนถึงวันที่ 31 ธันวาคม 2555 เป็นอย่างน้อย ทุกปีในตารางวันที่ของคุณจะต้องมีวันทั้งหมดของแต่ละปี ถ้าคุณจะรีเฟรชข้อมูลของคุณเป็นประจําด้วยข้อมูลที่ใหม่กว่า คุณอาจต้องการเรียกใช้วันที่สิ้นสุดครั้งละหนึ่งหรือสองปี เพื่อที่คุณจะได้ไม่ต้องอัปเดตตารางวันที่ของคุณเมื่อเวลาผ่านไป

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

ถ้าคุณรายงานเกี่ยวกับปีงบประมาณ คุณสามารถสร้างตารางวันที่ที่มีชุดวันที่ที่ต่อเนื่องกันสําหรับแต่ละปีงบประมาณ ตัวอย่างเช่น ถ้าปีงบประมาณของคุณเริ่มต้นในวันที่ 1 มีนาคม และคุณมีข้อมูลสําหรับปีงบประมาณ 2553 จนถึงวันที่ปัจจุบัน (ตัวอย่างเช่น ในปีงบประมาณ 2556) คุณสามารถสร้างตารางวันที่ที่เริ่มต้นในวันที่ 3/1/2552 และรวมอย่างน้อยทุกวันในแต่ละปีงบประมาณจนถึงวันสุดท้ายในปีงบประมาณ 2556

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

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

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

• นําเข้าจากฐานข้อมูลเชิงสัมพันธ์ หรือแหล่งข้อมูลอื่น
• สร้างตารางวันที่ใน Excel แล้วคัดลอกหรือลิงก์ไปยังตารางใหม่ใน Power Pivot
• นําเข้าจาก Microsoft Azure Marketplace

ลองมาดูแต่ละอย่างนี้อย่างใกล้ชิดยิ่งขึ้น

นําเข้าจากฐานข้อมูลเชิงสัมพันธ์

ถ้าคุณนําเข้าข้อมูลบางส่วนหรือทั้งหมดจากคลังข้อมูลหรือฐานข้อมูลเชิงสัมพันธ์ชนิดอื่น เป็นไปได้ว่าจะมีตารางวันที่และความสัมพันธ์ระหว่างตารางวันที่กับข้อมูลที่เหลือที่คุณกําลังนําเข้าอยู่แล้ว วันที่และรูปแบบน่าจะตรงกับวันที่ในข้อมูลข้อเท็จจริงของคุณ และวันที่อาจเริ่มต้นได้ดีในอดีตและในอนาคตอันไกลโพ้น ตารางวันที่ที่คุณต้องการนําเข้าอาจมีขนาดใหญ่มาก และมีช่วงของวันที่เกินกว่าที่คุณต้องการรวมในตัวแบบข้อมูลของคุณ คุณสามารถใช้ฟีเจอร์ตัวกรองขั้นสูงของตัวช่วยสร้างการนําเข้าตารางของ Power Pivot เพื่อเลือกเฉพาะวันที่และคอลัมน์เฉพาะที่คุณต้องการจริงๆ ได้ ซึ่งสามารถลดขนาดของเวิร์กบุ๊กและปรับปรุงประสิทธิภาพการทํางานได้อย่างมาก

ตัวช่วยสร้างการนําเข้าตาราง

ในกรณีส่วนใหญ่ คุณไม่จําเป็นต้องสร้างคอลัมน์เพิ่มเติม เช่น ปีงบประมาณ สัปดาห์ ชื่อเดือน และอื่นๆ เนื่องจากจะมีอยู่แล้วในตารางที่นําเข้า อย่างไรก็ตาม ในบางกรณี หลังจากที่คุณได้นําเข้าตารางวันที่ลงในตัวแบบข้อมูลของคุณแล้ว คุณอาจต้องสร้างคอลัมน์วันที่เพิ่มเติม โดยขึ้นอยู่กับความต้องการด้านการรายงานเฉพาะ โชคดีที่ใช้ DAX คุณสามารถทําเช่นนี้ได้อย่างง่ายดาย คุณจะได้เรียนรู้เพิ่มเติมเกี่ยวกับการสร้างเขตข้อมูลตารางวันที่ในภายหลัง ทุกสภาพแวดล้อมแตกต่างกัน ถ้าคุณไม่แน่ใจว่าแหล่งข้อมูลของคุณมีวันที่หรือตารางปฏิทินที่เกี่ยวข้องหรือไม่ ให้ปรึกษาผู้ดูแลระบบฐานข้อมูลของคุณ

สร้างตารางวันที่ใน Excel

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

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

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

1. ใน Excel ในเวิร์กชีตเปล่า ในเซลล์ A1 ให้พิมพ์ชื่อส่วนหัวของคอลัมน์เพื่อระบุช่วงของวันที่ โดยทั่วไปจะเป็น Date, DateTime หรือ DateKey

2. ในเซลล์ A2 ให้พิมพ์วันที่เริ่มต้น ตัวอย่างเช่น 1/1/2010

3. คลิกที่จุดจับเติม แล้วลากลงไปที่หมายเลขแถวที่มีวันที่สิ้นสุด ตัวอย่างเช่น 31/12/2016
   

4. เลือกแถวทั้งหมดในคอลัมน์ วันที่ (รวมถึงชื่อส่วนหัวในเซลล์ A1)

5. ในกลุ่ม สไตล์ ให้คลิก จัดรูปแบบเป็นตาราง แล้วเลือกสไตล์

6. ในกล่องโต้ตอบ จัดรูปแบบเป็นตาราง ให้คลิก ตกลง
   

7. คัดลอกแถวทั้งหมด รวมถึงส่วนหัวด้วย

8. ใน Power Pivot บนแท็บ หน้าแรก ให้คลิก วาง

9. ใน วางตัวอย่าง>ชื่อตาราง พิมพ์ชื่อ เช่น วันที่ หรือ Calendar ปล่อยให้เลือกใช้แถวแรกเป็นส่วนหัวของคอลัมน์แล้วคลิกตกลง
   
   ตารางวันที่ใหม่ (ในตัวอย่างนี้คือ Calendar) ใน Power Pivot จะมีลักษณะดังนี้
   

   หมายเหตุ

   คุณยังสามารถสร้างตารางที่ลิงก์โดยใช้ เพิ่มลงในตัวแบบข้อมูล อย่างไรก็ตาม การทําเช่นนี้จะทําให้เวิร์กบุ๊กของคุณมีขนาดใหญ่เกินความจําเป็นเนื่องจากเวิร์กบุ๊กมีตารางวันที่สองเวอร์ชัน หนึ่งใน Excel และอีกหนึ่งใน Power Pivot

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

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

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

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

ฟังก์ชันวันที่และเวลาของ DAX

ถ้าคุณเคยทํางานกับฟังก์ชันวันที่และเวลาในสูตร Excel คุณอาจคุ้นเคยกับฟังก์ชันวันที่และเวลา แม้ว่าฟังก์ชันเหล่านี้จะคล้ายกับฟังก์ชันต่างๆ ใน Excel แต่ก็มีความแตกต่างที่สําคัญบางประการ ดังนี้

• ฟังก์ชันวันที่และเวลาของ DAX จะใช้ชนิดข้อมูลวันที่และเวลา
• They can take values from a column as a argument
• โดยสามารถใช้เพื่อส่งกลับ และ/หรือจัดการค่าวันที่ได้

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

DAX มีฟังก์ชันวันที่และเวลาต่อไปนี้:

• วันที่
• DATEVALUE
• วันถัดไป
• EDATE
• EOMONTH
• ชั่วโมง
• นาที
• เดือน
• NOW
• วินาที
• TIME
• TIMEVALUE
• วันนี้
• WEEKDAY
• WEEKNUM
• ปี
• YEARFRAC

มีฟังก์ชัน DAX อื่นๆ อีกมากมายที่คุณสามารถใช้ได้ในสูตรของคุณเช่นกัน ตัวอย่างเช่น สูตรจํานวนมากที่อธิบายไว้ที่นี่จะใช้ ฟังก์ชันทางคณิตศาสตร์และตรีโกณมิติ เช่น MOD และ TRUNC ฟังก์ชัน ตรรกะ เช่น IF และ ฟังก์ชันข้อความ เช่น FORMAT สําหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน DAX อื่นๆ ให้ดูส่วน แหล่งข้อมูลเพิ่มเติม ภายหลังในบทความนี้

ตัวอย่างสูตรสําหรับปีปฏิทิน

ตัวอย่างต่อไปนี้อธิบายสูตรที่ใช้สร้างคอลัมน์เพิ่มเติมในตารางวันที่ที่ชื่อ Calendar คอลัมน์หนึ่งที่ชื่อว่า Date มีอยู่แล้ว และมีช่วงของวันที่ที่ต่อเนื่องกันตั้งแต่ 1/1/2010 ถึง 31/12/2016

ปี

=YEAR([date])

ในสูตรนี้ ฟังก์ชัน YEAR จะส่งกลับปีจากค่าในคอลัมน์ Date เนื่องจากค่าในคอลัมน์ วันที่ เป็นชนิดข้อมูลวันที่เวลา ฟังก์ชัน YEAR จะทราบวิธีการส่งกลับปีจากค่าดังกล่าว

เดือน

=MONTH([date])

ในสูตรนี้ เช่นเดียวกับฟังก์ชัน YEAR เราสามารถใช้ฟังก์ชัน MONTH เพื่อส่งกลับค่าเดือนจากคอลัมน์วันที่

ไตรมาส

=INT(([เดือน]+2)/3)

ในสูตรนี้ เราใช้ฟังก์ชัน INT เพื่อส่งกลับค่าวันที่เป็นจํานวนเต็ม อาร์กิวเมนต์ที่เราระบุสําหรับฟังก์ชัน INT คือค่าจากคอลัมน์ Month บวก 2 แล้วหารด้วย 3 เพื่อได้ไตรมาสของเรา 1 ถึง 4

ชื่อเดือน

=FORMAT([date],"mmmm")

ในสูตรนี้ เพื่อรับชื่อเดือน เราใช้ฟังก์ชัน FORMAT เพื่อแปลงค่าตัวเลขจากคอลัมน์วันที่เป็นข้อความ เราระบุคอลัมน์ วันที่ เป็นอาร์กิวเมนต์แรก แล้วตามด้วยรูปแบบ เราต้องการให้ชื่อเดือนของเราแสดงอักขระทั้งหมด ดังนั้นเราจึงใช้ "MMMM" ผลลัพธ์ของเรามีลักษณะดังนี้:

ถ้าเราต้องการส่งกลับชื่อเดือนที่ย่อเป็นตัวอักษรสามตัว เราจะใช้ "mmm" ในอาร์กิวเมนต์รูปแบบ

วันของสัปดาห์

=FORMAT([date],"ddd")

ในสูตรนี้ เราใช้ฟังก์ชัน FORMAT เพื่อรับชื่อวัน เนื่องจากเราเพียงต้องการชื่อวันแบบย่อ เราจึงระบุ "ddd" ในอาร์กิวเมนต์รูปแบบ

PivotTable ตัวอย่าง

เมื่อคุณมีเขตข้อมูลสําหรับวันที่แล้ว เช่น ปี ไตรมาส เดือน เป็นต้น คุณสามารถใช้เขตข้อมูลเหล่านั้นใน PivotTable หรือรายงานได้ ตัวอย่างเช่น รูปต่อไปนี้แสดงเขตข้อมูล SalesAmount จากตารางข้อเท็จจริงของ Sales ใน VALUES และ Year และ Quarter จากตารางมิติ Calendar ใน ROWS SalesAmount จะถูกรวมสําหรับบริบทของปีและไตรมาส

ตัวอย่างสูตรสําหรับปีงบประมาณ

ปีงบประมาณ

=IF([เดือน]<= 6,[ปี],[ปี]+1)

ในตัวอย่างนี้ ปีงบประมาณเริ่มต้นในวันที่ 1 กรกฎาคม

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

อีกวิธีหนึ่งในการระบุค่าของเดือนสิ้นสุดปีงบประมาณคือการสร้างหน่วยวัดที่ระบุเดือนเพียงอย่างเดียว ตัวอย่างเช่น FYE:=6 จากนั้น คุณสามารถอ้างอิงชื่อหน่วยวัดแทนหมายเลขเดือนได้ ตัวอย่างเช่น =IF([Month]<=[FYE],[Year],[Year]+1) ซึ่งให้ความยืดหยุ่นมากขึ้นเมื่ออ้างอิงเดือนสิ้นสุดปีงบประมาณในสูตรต่างๆ มากมาย

เดือนงบประมาณ

=IF([เดือน]=< 6, 6+[เดือน], [เดือน]- 6)

ในสูตรนี้เราระบุว่าค่าสําหรับ [เดือน] น้อยกว่าหรือเท่ากับ 6 ให้นํา 6 และบวกค่าจากเดือน มิฉะนั้นให้ลบ 6 จากค่าจาก [เดือน]

ไตรมาสงบประมาณ

=INT(([FiscalMonth]+2)/3)

สูตรที่เราใช้สําหรับไตรมาสงบประมาณจะเหมือนกับสูตรที่ใช้ในไตรมาสในปีปฏิทินของเรา ความแตกต่างเพียงข้อเดียวคือ เราระบุ [FiscalMonth] แทน [Month]

วันหยุดหรือวันที่พิเศษ

คุณอาจต้องการรวมคอลัมน์วันที่ที่ระบุวันที่บางวันที่เป็นวันหยุดหรือวันที่พิเศษอื่นๆ ตัวอย่างเช่น คุณอาจต้องการรวมยอดขายรวมสําหรับวันปีใหม่ โดยการเพิ่มเขตข้อมูล วันหยุด ลงใน PivotTable เป็นตัวแบ่งส่วนข้อมูล หรือตัวกรอง ในกรณีอื่นๆ คุณอาจต้องการแยกวันที่เหล่านั้นออกจากคอลัมน์วันที่อื่นๆ หรือในการวัด

การรวมวันหยุดหรือวันพิเศษนั้นค่อนข้างง่าย คุณสามารถสร้างตารางใน Excel ที่มีวันที่ที่คุณต้องการรวมได้ จากนั้นคุณสามารถคัดลอกหรือใช้ เพิ่มลงในตัวแบบข้อมูล เพื่อเพิ่มลงในตัวแบบข้อมูลเป็นตารางที่ลิงก์ได้ ในกรณีส่วนใหญ่ คุณไม่จําเป็นต้องสร้างความสัมพันธ์ระหว่างตารางและตาราง Calendar สูตรใดๆ ที่อ้างอิงสามารถใช้ฟังก์ชัน LOOKUPVALUE เพื่อส่งกลับค่าได้

ด้านล่างนี้คือตัวอย่างของตารางที่สร้างขึ้นใน Excel ที่มีวันหยุดที่จะเพิ่มลงในตารางวันที่

วันที่	วันหยุด
1/1/2010	ปีใหม่
11/25/2010	วันขอบคุณพระเจ้า
12/25/2010	คริสต์มาส
1/1/2554	ปีใหม่
11/24/2011	วันขอบคุณพระเจ้า
12/25/2011	คริสต์มาส
1/1/2555	ปีใหม่
22/11/2555	วันขอบคุณพระเจ้า
12/25/2012	คริสต์มาส
1/1/2013	ปีใหม่
11/28/2013	วันขอบคุณพระเจ้า
12/25/2013	คริสต์มาส
11/27/2014	วันขอบคุณพระเจ้า
12/25/2014	คริสต์มาส
1/1/2557	ปีใหม่
11/27/2014	วันขอบคุณพระเจ้า
12/25/2014	คริสต์มาส
1/1/2015	ปีใหม่
11/26/2014	วันขอบคุณพระเจ้า
12/25/2015	คริสต์มาส
1/1/2559	ปีใหม่
11/24/2016	วันขอบคุณพระเจ้า
12/25/2016	คริสต์มาส

ในตารางวันที่ เราจะสร้างคอลัมน์ชื่อ วันหยุด และใช้สูตรดังนี้

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

ลองพิจารณาสูตรนี้อย่างละเอียดมากขึ้น

เราใช้ฟังก์ชัน LOOKUPVALUE เพื่อรับค่าจากคอลัมน์วันหยุดในตารางวันหยุด ในอาร์กิวเมนต์แรก เราจะระบุคอลัมน์ที่มีค่าผลลัพธ์ของเรา เราระบุคอลัมน์ วันหยุด ในตาราง วันหยุด เพราะเป็นค่าที่เราต้องการให้ส่งกลับ

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

คอลัมน์วันหยุดจะส่งกลับชื่อวันหยุดสําหรับแต่ละแถวที่มีค่าวันที่ที่ตรงกับวันที่ในตารางวันหยุด

ปฏิทินแบบกําหนดเอง - สิบสามช่วงสี่สัปดาห์

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

ในตัวอย่างด้านล่าง ช่วงเวลาเต็มแรกจะเริ่มต้นในวันอาทิตย์แรกของปีงบประมาณ ในกรณีนี้ ปีงบประมาณเริ่มต้นในวันที่ 1 กรกฎาคม

สัปดาห์

ค่านี้ทําให้เรามีหมายเลขสัปดาห์ที่เริ่มต้นด้วยสัปดาห์เต็มสัปดาห์แรกในปีงบประมาณ ในตัวอย่างนี้ สัปดาห์แรกจะเริ่มต้นจากวันอาทิตย์ ดังนั้นสัปดาห์เต็มสัปดาห์แรกในปีงบประมาณแรกในตาราง Calendar จะเริ่มในวันที่ 4/7/2553 และต่อเนื่องไปจนถึงสัปดาห์สุดท้ายเต็มในตาราง Calendar แม้ว่าค่านี้จะไม่มีประโยชน์ในการวิเคราะห์มากนัก แต่ก็จําเป็นต้องคํานวณเพื่อใช้ในสูตรช่วง 28 วันอื่นๆ

=INT([date]-40356)/7)

ลองพิจารณาสูตรนี้อย่างละเอียดมากขึ้น

ก่อนอื่น เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์วันที่เป็นจํานวนเต็ม ดังนี้

=INT([date])

จากนั้นเราต้องการค้นหาวันอาทิตย์แรกของปีงบประมาณแรก เราเห็นว่ามันคือ 7/4/2010

ตอนนี้ให้ลบ 40356 (ซึ่งเป็นจํานวนเต็มของ 6/27/2010 วันอาทิตย์สุดท้ายจากปีงบประมาณก่อนหน้า) จากค่านั้นเพื่อให้ได้จํานวนวันนับตั้งแต่วันเริ่มต้นในตาราง Calendar ของเรา ดังนี้

=INT([date]-40356)

จากนั้นหารผลลัพธ์ด้วย 7 (วันในหนึ่งสัปดาห์) ดังนี้

=INT(([date]-40356)/7)

ผลลัพธ์มีลักษณะดังนี้:

เครื่องหมายมหัพภาค

ช่วงเวลาในปฏิทินแบบกําหนดเองนี้มี 28 วัน และจะเริ่มต้นในวันอาทิตย์เสมอ คอลัมน์นี้จะส่งกลับตัวเลขของช่วงเวลาที่เริ่มต้นด้วยวันอาทิตย์แรกในปีงบประมาณแรก

=INT(([สัปดาห์]+3)/4)

ลองพิจารณาสูตรนี้อย่างละเอียดมากขึ้น

ก่อนอื่น เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์ สัปดาห์ เป็นจํานวนเต็ม ดังนี้

= INT([สัปดาห์])

จากนั้นบวก 3 กับค่านั้น ดังนี้

=INT([สัปดาห์]+3)

จากนั้นหารผลลัพธ์ด้วย 4 ดังนี้

=INT(([สัปดาห์]+3)/4)

ผลลัพธ์มีลักษณะดังนี้:

ช่วงเวลา ปีงบประมาณ

ค่านี้จะส่งกลับปีงบประมาณของช่วงเวลาหนึ่ง

=INT(([ระยะเวลา]+12)/13)+2008

ลองพิจารณาสูตรนี้อย่างละเอียดมากขึ้น

ขั้นแรก เราจะสร้างสูตรที่ส่งกลับค่าจากเครื่องหมายมหัพภาคและบวก 12:

=([ระยะเวลา]+12)

เราจะหารผลลัพธ์ด้วย 13 เนื่องจากมีช่วงเวลา 28 วันสิบสามช่วงในปีงบประมาณ:

=(([ระยะเวลา]+12)/13)

เราเพิ่ม 2010 เนื่องจากเป็นปีแรกในตาราง:

=(([ระยะเวลา]+12)/13)+2010

สุดท้าย เราใช้ฟังก์ชัน INT เพื่อลบเศษส่วนของผลลัพธ์ แล้วส่งกลับจํานวนเต็มเมื่อหารด้วย 13 ดังนี้:

= INT(([ระยะเวลา]+12)/13)+2010

ผลลัพธ์มีลักษณะดังนี้:

ระยะเวลาในปีงบประมาณ

ค่านี้จะส่งกลับหมายเลขช่วงเวลา 1 – 13 โดยเริ่มต้นด้วยช่วงเวลาเต็มแรก (เริ่มต้นในวันอาทิตย์) ในแต่ละปีงบประมาณ

=IF(MOD([ระยะเวลา],13), MOD([ระยะเวลา],13),13)

สูตรนี้ค่อนข้างซับซ้อนกว่าเล็กน้อย ดังนั้นเราจะอธิบายสูตรนี้ก่อนในภาษาที่เราเข้าใจได้ดีขึ้น สูตรนี้ระบุว่า ให้หารค่าจาก [คาบเวลา] ด้วย 13 เพื่อหาจํานวนคาบเวลา (1-13) ในปีนั้น ถ้าตัวเลขเป็น 0 ให้ส่งกลับ 13

ก่อนอื่น เราจะสร้างสูตรที่ส่งกลับค่าที่เหลือจาก Period โดย 13 เราสามารถใช้ MOD (ฟังก์ชันทางคณิตศาสตร์และตรีโกณมิติ) ได้ดังนี้:

= MOD([ระยะเวลา],13)

โดยส่วนใหญ่แล้ว สิ่งนี้จะให้ผลลัพธ์ที่เราต้องการ ยกเว้นในกรณีที่ค่าสําหรับช่วงเวลาเป็น 0 เนื่องจากวันที่เหล่านั้นไม่ได้อยู่ภายในปีงบประมาณแรก เช่น ในห้าวันแรกของตารางวันที่ใน Calendar ตัวอย่างของเรา เราสามารถจัดการเรื่องนี้ได้ด้วยฟังก์ชัน IF ในกรณีที่ผลลัพธ์ของเราเป็น 0 เราจะส่งกลับ 13 ดังนี้:

= IF(MOD([ระยะเวลา],13),MOD([ระยะเวลา],13),13)

ผลลัพธ์มีลักษณะดังนี้:

PivotTable ตัวอย่าง

รูปด้านล่างแสดง PivotTable ที่มีเขตข้อมูล SalesAmount จากตารางข้อเท็จจริง Sales ใน VALUES และเขตข้อมูล PeriodFiscalYear และ PeriodInFiscalYear จากตารางมิติวันที่ของ Calendar ใน ROWS SalesAmount จะถูกรวมสําหรับบริบทตามปีงบประมาณและช่วงเวลา 28 วันในปีงบประมาณ

ความสัมพันธ์

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

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

สําหรับค่าวันที่ทุกค่าในตารางข้อเท็จจริง คอลัมน์การค้นหาที่เกี่ยวข้องในตารางวันที่ต้องมีค่าที่ตรงกัน ตัวอย่างเช่น แถว (ระเบียนธุรกรรม) ในตารางยอดขายข้อเท็จจริงที่มีค่า 8/15/2012 12:00 AM ในคอลัมน์ DateKey จะต้องมีค่าที่สอดคล้องกันในคอลัมน์ วันที่ ที่เกี่ยวข้องในตารางวันที่ (ชื่อ Calendar) นี่คือเหตุผลที่สําคัญที่สุดที่คุณต้องการให้คอลัมน์วันที่ในตารางวันที่มีช่วงของวันที่ที่ต่อเนื่องกันซึ่งมีวันที่ที่เป็นไปได้ในตารางข้อเท็จจริงของคุณ

ถ้าคุณได้รับข้อผิดพลาด ชนิดไม่ตรงกันเมื่อพยายามสร้างความสัมพันธ์ อาจเป็นไปได้ว่าคอลัมน์ในตารางข้อเท็จจริงไม่ใช่ชนิดข้อมูลวันที่ ซึ่งอาจเกิดขึ้นได้เมื่อ Power Pivot ไม่สามารถแปลงชนิดข้อมูลที่ไม่ใช่วันที่ (โดยปกติจะเป็นชนิดข้อมูล Text) เป็นชนิดข้อมูลวันที่ได้โดยอัตโนมัติ คุณยังสามารถใช้คอลัมน์ในตารางข้อเท็จจริงของคุณได้ แต่คุณจะต้องแปลงข้อมูลด้วยสูตร DAX ในคอลัมน์จากการคํานวณใหม่ ดูการแปลง วันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่ ในภาคผนวกต่อไป

หลายความสัมพันธ์

ในบางกรณี คุณอาจจําเป็นต้องสร้างความสัมพันธ์หลายรายการหรือสร้างตารางวันที่หลายตาราง ตัวอย่างเช่น ถ้ามีหลายเขตข้อมูลวันที่ในตารางยอดขายจริง เช่น DateKey, ShipDate และ ReturnDate เขตข้อมูลเหล่านั้นสามารถมีความสัมพันธ์กับเขตข้อมูลวันที่ในตารางวันที่ Calendar แต่มีเพียงเขตข้อมูลเดียวเท่านั้นที่สามารถเป็นความสัมพันธ์ที่ใช้งานได้ ในกรณีนี้ เนื่องจาก DateKey แสดงวันที่ของธุรกรรม และวันที่ที่สําคัญที่สุด จึงสามารถใช้เป็นความสัมพันธ์ที่ใช้งานได้ดีที่สุด คนอื่นๆ มีความสัมพันธ์ที่ไม่ได้ใช้งาน

PivotTable ต่อไปนี้จะคํานวณยอดขายรวมตามปีงบประมาณและไตรมาสงบประมาณ หน่วยวัดที่ชื่อ Total Sales ที่มีสูตร Total Sales:=SUM([SalesAmount]) จะถูกวางไว้ใน VALUES และเขตข้อมูล FiscalYear และ FiscalQuarter จากตารางวันที่ Calendar จะถูกวางไว้ใน ROWS

รายการ ของยอดขายรวมตามไตรมาสงบประมาณ

PivotTable แบบตรงไปตรงมานี้ทํางานอย่างถูกต้องเนื่องจากเราต้องการรวมยอดขายทั้งหมดของเราตาม transactiondate ใน DateKey หน่วยวัด Total Sales ใช้วันที่ใน DateKey และจะถูกรวมตามปีงบประมาณและไตรมาสงบประมาณ เนื่องจากมีความสัมพันธ์ระหว่าง DateKey ในตาราง ยอดขาย และคอลัมน์ วันที่ ในตารางวันที่ Calendar

ความสัมพันธ์ที่ไม่ได้ใช้งาน

แต่ถ้าเราต้องการรวมยอดขายรวมของเราไม่ใช่ตามวันที่ทําธุรกรรม แต่ตาม วันที่จัดส่งล่ะ เราต้องการความสัมพันธ์ระหว่างคอลัมน์ ShipDate ในตาราง Sales และคอลัมน์ Date ในตาราง Calendar ถ้าเราไม่ได้สร้างความสัมพันธ์ดังกล่าว การรวมของเราจะยึดตามวันที่ของธุรกรรมเสมอ อย่างไรก็ตาม เราสามารถมีความสัมพันธ์ได้หลายความสัมพันธ์ แม้ว่าจะมีเพียงความสัมพันธ์เดียวเท่านั้นที่สามารถใช้งานได้ และเนื่องจากวันที่ของธุรกรรมมีความสําคัญที่สุด วันที่ทําธุรกรรมจึงได้รับความสัมพันธ์ที่ใช้งานอยู่กับตาราง Calendar

ในกรณีนี้ ShipDate มีความสัมพันธ์ที่ไม่ได้ใช้งาน ดังนั้น สูตรวัดใดๆ ที่สร้างขึ้นเพื่อรวมข้อมูลโดยยึดตามวันที่จัดส่งจะต้องระบุความสัมพันธ์ที่ไม่ได้ใช้งานโดยใช้ฟังก์ชัน USERELATIONSHIP

ตัวอย่างเช่น เนื่องจากมีความสัมพันธ์ที่ไม่ได้ใช้งานระหว่างคอลัมน์ ShipDate ในตาราง Sales และคอลัมน์ Date ในตาราง Calendar เราสามารถสร้างหน่วยวัดที่รวมยอดขายทั้งหมดตามวันที่จัดส่งได้ เราใช้สูตรเช่นนี้เพื่อระบุความสัมพันธ์ที่จะใช้:

Total Sales by Shipped Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

สูตรนี้ระบุเพียงว่า คํานวณผลรวมสําหรับ SalesAmount แต่ให้กรองโดยใช้ความสัมพันธ์ระหว่างคอลัมน์ ShipDate ในตาราง Sales และคอลัมน์ Date ในตาราง Calendar

ทีนี้ ถ้าเราสร้าง PivotTable และใส่การวัดยอดขายรวมตามวันที่จัดส่งใน VALUES และปีงบประมาณและไตรมาสงบประมาณบน ROWS เราจะเห็นผลรวมทั้งหมดเหมือนกัน แต่จํานวนผลรวมอื่นๆ ทั้งหมดสําหรับปีงบประมาณและไตรมาสงบประมาณจะแตกต่างกัน เนื่องจากยึดตามวันที่จัดส่ง ไม่ใช่วันที่ทําธุรกรรม

รายการ ของยอดขายรวมตามวันที่จัดส่ง

การใช้ความสัมพันธ์ที่ไม่ได้ใช้งานจะอนุญาตให้คุณใช้ตารางวันที่ได้เพียงตารางเดียวเท่านั้น แต่จําเป็นต้องให้การวัดใดๆ (เช่น ยอดขายรวมตามวันที่จัดส่ง) อ้างอิงความสัมพันธ์ที่ไม่ได้ใช้งานในสูตร มีอีกทางเลือกหนึ่ง นั่นคือ ใช้ตารางวันที่หลายตาราง

หลายตารางวันที่

อีกวิธีหนึ่งในการทํางานกับคอลัมน์วันที่หลายคอลัมน์ในตารางข้อเท็จจริงของคุณคือการสร้างตารางวันที่หลายตาราง และสร้างความสัมพันธ์แบบแอบทีฟแยกกันระหว่างคอลัมน์เหล่านั้น มาดูตัวอย่างตาราง ยอดขาย ของเราอีกครั้ง เรามีสามคอลัมน์ที่มีวันที่ที่คุณอาจต้องการรวมข้อมูล:

• DateKey ที่มีวันที่ขายสําหรับแต่ละธุรกรรม
• วันที่จัดส่ง – ที่มีวันที่และเวลาที่สินค้าที่ขายถูกจัดส่งไปยังลูกค้า
• ReturnDate – ที่มีวันที่และเวลาที่ได้รับสินค้าอย่างน้อยหนึ่งรายการ

โปรดทราบว่า เขตข้อมูล DateKey ที่มีวันที่ทําธุรกรรมจะสําคัญที่สุด เราจะทําการรวมส่วนใหญ่ของเราตามวันที่เหล่านี้ ดังนั้นเราจะต้องการความสัมพันธ์ระหว่างวันที่นี้กับคอลัมน์วันที่ในตาราง Calendar อย่างแน่นอน ถ้าเราไม่ต้องการสร้างความสัมพันธ์ที่ไม่ได้ใช้งานระหว่าง ShipDate และ ReturnDate และเขตข้อมูลวันที่ในตาราง Calendar ดังนั้นจึงต้องใช้สูตรวัดพิเศษ เราสามารถสร้างตารางวันที่เพิ่มเติมสําหรับวันที่จัดส่งและวันที่ส่งคืนได้ จากนั้นเราก็สามารถสร้างความสัมพันธ์ที่กระตือรือร้นระหว่างพวกเขาได้

ในตัวอย่างนี้ เราได้สร้างตารางวันที่อื่นที่ชื่อว่า ShipCalendar แน่นอนว่านี่ยังหมายถึงการสร้างคอลัมน์วันที่เพิ่มเติม และเนื่องจากคอลัมน์วันที่เหล่านี้อยู่ในตารางวันที่ที่แตกต่างกัน เราจึงต้องการตั้งชื่อคอลัมน์ด้วยวิธีที่แตกต่างจากคอลัมน์เดียวกันในตาราง Calendar ตัวอย่างเช่น เราได้สร้างคอลัมน์ที่ชื่อ ShipYear, ShipMonth, ShipQuarter และอื่นๆ

ถ้าเราสร้าง PivotTable ของเราและใส่การวัดยอดขายรวมของเราใน VALUES และ ShipFiscalYear และ ShipFiscalQuarter บน ROWS เราจะเห็นผลลัพธ์แบบเดียวกับที่เราเห็นเมื่อเราสร้างความสัมพันธ์ที่ไม่ได้ใช้งานและเขตข้อมูลการคํานวณพิเศษ ยอดขายรวมตามวันที่จัดส่ง

แต่ละแนวทางเหล่านี้ต้องพิจารณาอย่างรอบคอบ เมื่อใช้ความสัมพันธ์หลายรายการกับตารางวันที่ตารางเดียว คุณอาจต้องสร้างหน่วยวัดพิเศษที่ส่งผ่านความสัมพันธ์ที่ไม่ได้ใช้งานโดยใช้ฟังก์ชัน USERELATIONSHIP ในทางกลับกัน การสร้างตารางวันที่หลายตารางอาจสร้างความสับสนในรายการเขตข้อมูล และเนื่องจากคุณมีตารางเพิ่มเติมในตัวแบบข้อมูล จึงต้องใช้หน่วยความจํามากขึ้น ทดลองกับสิ่งที่เหมาะกับคุณมากที่สุด

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

คุณสมบัติตารางวันที่จะตั้งค่าเมตาดาต้าที่จําเป็นสําหรับฟังก์ชัน Time-Intelligence เช่น TOTALYTD, PREVIOUSMONTH และ DATESBETWEEN เพื่อให้ทํางานได้อย่างถูกต้อง เมื่อการคํานวณถูกเรียกใช้โดยใช้ฟังก์ชันใดฟังก์ชันหนึ่งเหล่านี้ กลไกสูตรของ Power Pivot จะทราบตําแหน่งสําหรับการดูวันที่ที่ต้องการ

เมื่อคุณตั้งค่าคุณสมบัติตารางวันที่ คุณจะระบุตารางวันที่และคอลัมน์วันที่ของชนิดข้อมูล วันที่ (วันที่และเวลา)

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

1. ในหน้าต่าง PowerPivot ให้เลือกตาราง Calendar
2. บนแท็บ ออกแบบ ให้คลิก ทําเครื่องหมายเป็นตารางวันที่
3. ในกล่องโต้ตอบ ทําเครื่องหมายเป็นตารางวันที่ ให้เลือกคอลัมน์ซึ่งมีค่าที่ไม่ซ้ํากันและชนิดข้อมูลวันที่

ทํางานกับเวลา

ค่าวันที่ทั้งหมดที่มีชนิดข้อมูลวันที่ใน Excel หรือ SQL Server คือตัวเลข รวมอยู่ในตัวเลขดังกล่าวเป็นตัวเลขที่อ้างถึงเวลา ในหลายกรณี เวลานั้นสําหรับแต่ละแถวคือเที่ยงคืน ตัวอย่างเช่น ถ้าเขตข้อมูล DateTimeKey ในตารางยอดขายจริงมีค่า เช่น 19/10/2010 12:00:00 AM นั่นหมายความว่าค่าอยู่ในระดับความแม่นยําของวัน ถ้าค่าเขตข้อมูล DateTimeKey มีเวลารวมอยู่ด้วย ตัวอย่างเช่น 19/10/2010 8:44:00 AM นั่นหมายความว่าค่าอยู่ในระดับนาทีของความแม่นยํา ค่าอาจเป็นความแม่นยําระดับชั่วโมง หรือแม้แต่ความแม่นยําระดับวินาที ระดับความแม่นยําในค่าเวลาจะมีผลกระทบอย่างมากต่อวิธีการสร้างตารางวันที่ของคุณ และความสัมพันธ์ระหว่างตารางนี้กับตารางข้อเท็จจริงของคุณ

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

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

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

ถ้าคอลัมน์วันที่ของคุณมีระดับความแม่นยําของเวลา แต่คุณจะรวมเฉพาะระดับวันเท่านั้น เพื่อสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงและตารางวันที่ คุณอาจต้องปรับเปลี่ยนตารางข้อเท็จจริงของคุณด้วยการสร้างคอลัมน์ใหม่ที่ตัดค่าในคอลัมน์วันที่เป็นค่าวัน กล่าวคือ แปลงค่า เช่น 19/10/2010 8:44:00AM เป็น 19/10/2010 12:00:00 AM จากนั้น คุณสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ใหม่นี้กับคอลัมน์วันที่ในตารางวันที่ได้ เนื่องจากค่าตรงกัน

ลองมาดูตัวอย่าง รูปนี้แสดงคอลัมน์ DateTimeKey ในตารางข้อเท็จจริงของยอดขาย การรวมข้อมูลทั้งหมดในตารางนี้ต้องใช้ในระดับวันเท่านั้น โดยใช้คอลัมน์ในตารางวันที่ใน Calendar เช่น Year, Month, Quarter เป็นต้น เวลาที่รวมอยู่ในค่าไม่เกี่ยวข้อง เฉพาะวันที่จริงเท่านั้น

เนื่องจากเราไม่จําเป็นต้องวิเคราะห์ข้อมูลนี้ในระดับเวลา เราจึงไม่ต้องการให้คอลัมน์วันที่ในตารางวันที่ Calendar รวมหนึ่งแถวสําหรับทุกชั่วโมงและทุกนาทีของทุกวันในแต่ละปี ดังนั้น คอลัมน์วันที่ในตารางวันที่จะมีลักษณะเช่นนี้:

เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTimeKey ในตาราง Sales และคอลัมน์ Date ในตาราง Calendar เราสามารถสร้างคอลัมน์จากการคํานวณใหม่ในตาราง Sales fact และใช้ฟังก์ชัน TRUNC เพื่อตัดค่าวันที่และเวลาในคอลัมน์ DateTimeKey ให้เป็นค่าวันที่ที่ตรงกับค่าในคอลัมน์ Date ในตาราง Calendar ได้ สูตรของเรามีลักษณะดังนี้:

=TRUNC([DateTimeKey],0)

การทําเช่นนี้ทําให้เรามีคอลัมน์ใหม่ (เราตั้งชื่อว่า DateKey) ที่มีวันที่จากคอลัมน์ DateTimeKey และเวลา 12:00:00 AM สําหรับแต่ละแถว:

ในตอนนี้ เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ (DateKey) ใหม่และคอลัมน์วันที่ในตาราง Calendar ได้

ในทํานองเดียวกัน เราสามารถสร้างคอลัมน์จากการคํานวณในตารางยอดขายที่ลดความแม่นยําของเวลาในคอลัมน์ DateTimeKey ไปสู่ระดับความแม่นยําชั่วโมง ในกรณีนี้ ฟังก์ชัน TRUNC จะไม่ทํางาน แต่เรายังคงสามารถใช้ฟังก์ชันวันที่และเวลาอื่นๆ ของ DAX เพื่อแยกและเชื่อมค่าใหม่ให้มีความแม่นยําในระดับชั่วโมง เราสามารถใช้สูตรดังนี้:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

คอลัมน์ใหม่ของเรามีลักษณะดังนี้:

หากคอลัมน์วันที่ของเราในตารางวันที่มีค่าในระดับความแม่นยําเป็นชั่วโมง เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์เหล่านั้นได้

การทําให้วันที่มีประโยชน์มากขึ้น

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

ในทํานองเดียวกัน ในตัวอย่างของเรา คอลัมน์วันที่ในตาราง Calendar มีประโยชน์มาก สําคัญมากในความเป็นจริง แต่คุณไม่สามารถใช้เป็นมิติใน PivotTable ได้

เมื่อต้องการให้ตารางและคอลัมน์ในตารางมีประโยชน์ที่สุดเท่าที่จะเป็นไปได้ และเพื่อทําให้รายการเขตข้อมูล PivotTable หรือรายงาน Power View ง่ายต่อการนําทาง การซ่อนคอลัมน์ที่ไม่จําเป็นจากเครื่องมือไคลเอ็นต์เป็นสิ่งสําคัญ คุณอาจต้องการซ่อนบางตารางด้วยเช่นกัน ตารางวันหยุดที่แสดงก่อนหน้านี้มีวันที่วันหยุดที่สําคัญสําหรับบางคอลัมน์ในตาราง Calendar แต่คุณไม่สามารถใช้คอลัมน์วันที่และวันหยุดในตารางวันหยุดเป็นเขตข้อมูลใน PivotTable ได้ ที่นี่ เพื่อทําให้รายการเขตข้อมูลง่ายต่อการนําทาง คุณสามารถซ่อนทั้งตารางวันหยุดได้

สิ่งสําคัญอีกประการหนึ่งของการทํางานกับวันที่คือการตั้งชื่อข้อตกลง คุณสามารถตั้งชื่อตารางและคอลัมน์ใน Power Pivot ตามที่คุณต้องการ แต่โปรดทราบว่า โดยเฉพาะอย่างยิ่งถ้าคุณจะแชร์เวิร์กบุ๊กของคุณกับผู้ใช้อื่น ข้อตกลงการตั้งชื่อที่ดีจะทําให้การระบุตารางและวันที่ง่ายขึ้น ไม่เฉพาะในรายการเขตข้อมูลเท่านั้น แต่ยังรวมถึงใน Power Pivot และสูตร DAX ด้วย

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

ภาคผนวก

การแปลงวันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูล Date

ในบางกรณี ตารางข้อเท็จจริงที่มีข้อมูลธุรกรรมอาจมีวันที่ของชนิดข้อมูลข้อความ กล่าวคือ วันที่ที่ปรากฏเป็น 2012-12-04T11:47:09 ในความเป็นจริงแล้วไม่ใช่วันที่เลย หรืออย่างน้อยก็ไม่ใช่ชนิดของวันที่ที่ Power Pivot เข้าใจได้ จริงๆ แล้วเป็นเพียงข้อความที่อ่านเหมือนวันที่ เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์วันที่ในตารางข้อเท็จจริงและคอลัมน์วันที่ในตารางวันที่ ทั้งสองคอลัมน์ต้องเป็นชนิดข้อมูลวันที่

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

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

ในตัวอย่างนี้ เราได้นําเข้าตารางข้อเท็จจริงที่ชื่อ Sales ลงใน Power Pivot ซึ่งจะมีคอลัมน์ชื่อ วันที่เวลา ค่าจะปรากฏดังนี้:

ถ้าเราดูที่ ชนิดข้อมูล ในกลุ่ม การจัดรูปแบบ แท็บ หน้าแรก ของ Power Pivot เราจะเห็นว่าเป็นชนิดข้อมูล Text

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

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

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

=DATE(LEFT([วันที่เวลา],4), MID([วันที่และเวลา],6,2), MID([วันที่และเวลา],9,2))

การทําเช่นนี้ทําให้เรามีคอลัมน์ใหม่ (ในกรณีนี้เรียกว่า วันที่) Power Pivot จะตรวจหาค่าเหล่านั้นให้เป็นวันที่ และจะตั้งค่าชนิดข้อมูลให้เป็น Date โดยอัตโนมัติ

ถ้าเราต้องการรักษาระดับความแม่นยําของเวลา เราเพียงแค่ขยายสูตรเพื่อรวมชั่วโมง นาที และวินาที

=DATE(LEFT([วันที่เวลา],4), MID([วันที่เวลา],6,2), MID([วันที่เวลา],9,2)) +

TIME(MID([วันที่และเวลา],12,2), MID([วันที่และเวลา],15,2), MID([วันที่และเวลา],18,2))

ตอนนี้เรามีคอลัมน์วันที่ของชนิดข้อมูลวันที่ เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์วันที่กับคอลัมน์วันที่ในวันที่ได้

แหล่งข้อมูลเพิ่มเติม

วันที่ใน Power Pivot

การคำนวณใน Power Pivot

การเริ่มต้นใช้งานด่วนt: เรียนรู้ข้อมูลพื้นฐานเกี่ยวกับ DAX ภายใน 30 นาที

การอ้างอิงนิพจน์การวิเคราะห์ข้อมูล

ศูนย์ทรัพยากร DAX