ตารางวันที่ใน Power Pivot เป็นสิ่งจําเป็นสําหรับการเรียกดูและการคํานวณข้อมูลตามช่วงเวลา บทความนี้จะให้ความเข้าใจอย่างถี่ถ้วนเกี่ยวกับตารางวันที่และวิธีที่คุณสามารถสร้างตารางเหล่านั้นใน Power Pivot โดยเฉพาะอย่างยิ่ง บทความนี้จะอธิบายเกี่ยวกับ:
-
เหตุใดตารางวันที่จึงสําคัญสําหรับการเรียกดูและคํานวณข้อมูลตามวันที่และเวลา
-
วิธีใช้ Power Pivot เพื่อเพิ่มตารางวันที่ลงในตัวแบบข้อมูล
-
วิธีการสร้างคอลัมน์วันที่ใหม่ เช่น ปี เดือน และช่วงเวลาในตารางวันที่
-
วิธีการสร้างความสัมพันธ์ระหว่างตารางวันที่และตารางข้อเท็จจริง
-
วิธีการทํางานกับเวลา
บทความนี้มีไว้สําหรับผู้ใช้ใหม่ใน Power Pivot อย่างไรก็ตาม คุณควรมีความเข้าใจที่ดีในการนําเข้าข้อมูล การสร้างความสัมพันธ์ และการสร้างคอลัมน์และการวัดจากการคํานวณ
บทความนี้ ไม่ได้ อธิบายวิธีใช้ฟังก์ชัน Time-Intelligence DAX ในสูตรการวัด สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีสร้างการวัดด้วยฟังก์ชันตัวแสดงเวลา DAX ให้ดูที่ ตัวแสดงเวลาใน Power Pivot ใน Excel
หมายเหตุ: ใน Power Pivot ชื่อ "การวัด" และ "เขตข้อมูลจากการคํานวณ" จะเป็นคําพ้องความหมาย เรากําลังใช้การวัดชื่อตลอดบทความนี้ สําหรับข้อมูลเพิ่มเติม ให้ดู การวัดใน Power Pivot
เนื้อหา
การทําความเข้าใจตารางวันที่
การวิเคราะห์ข้อมูลเกือบทั้งหมดเกี่ยวข้องกับการเรียกดูและการเปรียบเทียบข้อมูลตามวันที่และเวลา ตัวอย่างเช่น คุณอาจต้องการรวมยอดขายสําหรับไตรมาสบัญชีที่ผ่านมา แล้วเปรียบเทียบผลรวมเหล่านั้นกับไตรมาสอื่น หรือคุณอาจต้องการคํานวณดุลปิดบัญชีสิ้นเดือนสําหรับบัญชี ในแต่ละกรณีเหล่านี้ คุณกําลังใช้วันที่เป็นวิธีหนึ่งในการจัดกลุ่มและรวมธุรกรรมการขายหรือดุลสําหรับรอบระยะเวลาเฉพาะในเวลา
รายงาน Power View
ตารางวันที่สามารถมีการแสดงวันที่และเวลาที่แตกต่างกันได้หลายแบบ ตัวอย่างเช่น ตารางวันที่มักจะมีคอลัมน์ เช่น ปีงบประมาณ เดือน ไตรมาส หรือ ช่วงเวลา ซึ่งคุณสามารถเลือกเป็นเขตข้อมูลจากรายการเขตข้อมูลเมื่อแบ่งและกรองข้อมูลของคุณในรายงาน PivotTable หรือ Power View
รายการเขตข้อมูล Power View
สําหรับคอลัมน์วันที่ เช่น ปี เดือน และไตรมาส เพื่อรวมวันที่ทั้งหมดภายในช่วงที่เกี่ยวข้อง ตารางวันที่ ต้องมี อย่างน้อยหนึ่งคอลัมน์ที่มีชุดของวันที่ที่ติดกัน นั่นคือ คอลัมน์นั้นต้องมีหนึ่งแถวสําหรับทุกวันสําหรับแต่ละปีที่รวมอยู่ในตารางวันที่
ตัวอย่างเช่น ถ้าข้อมูลที่คุณต้องการเรียกดูมีวันที่ตั้งแต่วันที่ 1 กุมภาพันธ์ 2553 ถึงวันที่ 30 พฤศจิกายน 2555 และคุณรายงานเกี่ยวกับปีปฏิทิน คุณจะต้องใช้ตารางวันที่ที่มีช่วงวันที่ตั้งแต่วันที่ 1 มกราคม 2553 ถึง 31 ธันวาคม 2555 เป็นอย่างน้อย ทุกปีในตารางวันที่ของคุณต้องมีวันทั้งหมดสําหรับแต่ละปี ถ้าคุณจะรีเฟรชข้อมูลของคุณด้วยข้อมูลที่ใหม่กว่าเป็นประจํา คุณอาจต้องการเรียกใช้วันที่สิ้นสุดเป็นหนึ่งหรือสองปี ดังนั้น คุณจึงไม่จําเป็นต้องอัปเดตตารางวันที่ของคุณเมื่อเวลาผ่านไป
ตารางวันที่ที่มีชุดของวันที่ที่ติดกัน
ถ้าคุณรายงานในปีบัญชี คุณสามารถสร้างตารางวันที่ที่มีชุดวันที่ที่ต่อเนื่องกันสําหรับแต่ละปีบัญชีได้ ตัวอย่างเช่น ถ้าปีงบประมาณของคุณเริ่มต้นในวันที่ 1 มีนาคม และคุณมีข้อมูลสําหรับปีงบประมาณ 2553 จนถึงวันที่ปัจจุบัน (ตัวอย่างเช่น ในปี 2556) คุณสามารถสร้างตารางวันที่ที่เริ่มต้นในวันที่ 1/3/2552 และรวมอย่างน้อยหนึ่งวันในแต่ละปีงบประมาณจนถึงวันที่สุดท้ายในปีงบประมาณ 2556
ถ้าคุณจะรายงานทั้งปีปฏิทินและปีบัญชี คุณไม่จําเป็นต้องสร้างตารางวันที่แยกต่างหาก ตารางวันที่เดียวสามารถรวมคอลัมน์สําหรับปีปฏิทิน ปีงบประมาณ และปฏิทินช่วงเวลาสิบสามสัปดาห์ได้ สิ่งสําคัญคือตารางวันที่ของคุณมีชุดวันที่ที่ติดกันสําหรับปีทั้งหมดรวมอยู่ด้วย
การเพิ่มตารางวันที่ลงในตัวแบบข้อมูล
คุณสามารถเพิ่มตารางวันที่ลงในตัวแบบข้อมูลของคุณได้หลายวิธีดังนี้
-
นําเข้าจากฐานข้อมูลเชิงสัมพันธ์หรือแหล่งข้อมูลอื่น
-
สร้างตารางวันที่ใน Excel แล้วคัดลอกหรือลิงก์ไปยังตารางใหม่ใน Power Pivot
-
นําเข้าจาก Microsoft Azure Marketplace
มาดูแต่ละอย่างให้ละเอียดยิ่งขึ้นกัน
นําเข้าจากฐานข้อมูลเชิงสัมพันธ์
ถ้าคุณนําเข้าข้อมูลบางส่วนหรือทั้งหมดของคุณจากคลังสินค้าข้อมูลหรือฐานข้อมูลเชิงสัมพันธ์ชนิดอื่น เป็นไปได้ว่าจะมีตารางวันที่และความสัมพันธ์ระหว่างตารางกับข้อมูลที่เหลือที่คุณกําลังนําเข้าอยู่แล้ว วันที่และรูปแบบน่าจะตรงกับวันที่ในข้อมูลจริงของคุณ และวันที่อาจเริ่มต้นได้ดีในอดีตและออกไปในอนาคต ตารางวันที่ที่คุณต้องการนําเข้าอาจมีขนาดใหญ่มาก และมีช่วงของวันที่ที่นอกเหนือจากสิ่งที่คุณจะต้องใส่ในตัวแบบข้อมูลของคุณ คุณสามารถใช้ฟีเจอร์ตัวกรองขั้นสูงของตัวช่วยสร้างการนําเข้าตารางของ Power Pivot เพื่อเลือกเฉพาะวันที่และคอลัมน์เฉพาะที่คุณต้องการจริงๆ วิธีนี้สามารถลดขนาดเวิร์กบุ๊กของคุณและปรับปรุงประสิทธิภาพการทํางานได้อย่างมาก
ตัวช่วยสร้างการนําเข้าตาราง
ในกรณีส่วนใหญ่ คุณไม่จําเป็นต้องสร้างคอลัมน์เพิ่มเติม ใดๆ เช่น ปีงบประมาณ สัปดาห์ ชื่อเดือน เป็นต้น เนื่องจากคอลัมน์เหล่านั้นจะมีอยู่แล้วในตารางที่นําเข้า อย่างไรก็ตาม ในบางกรณี หลังจากที่คุณนําเข้าตารางวันที่ลงในตัวแบบข้อมูลของคุณแล้ว คุณอาจต้องสร้างคอลัมน์วันที่เพิ่มเติม โดยขึ้นอยู่กับความต้องการด้านการรายงานบางอย่าง โชคดีที่การใช้ DAX ทําได้ง่าย คุณจะได้เรียนรู้เพิ่มเติมเกี่ยวกับการสร้างเขตข้อมูลตารางวันที่ในภายหลัง ทุกสภาพแวดล้อมมีความแตกต่างกัน ถ้าคุณไม่แน่ใจว่าแหล่งข้อมูลของคุณมีตารางวันที่หรือปฏิทินที่เกี่ยวข้องหรือไม่ ให้ปรึกษาผู้ดูแลระบบฐานข้อมูลของคุณ
สร้างตารางวันที่ใน Excel
คุณสามารถสร้างตารางวันที่ใน Excel แล้วคัดลอกลงในตารางใหม่ในตัวแบบข้อมูล สิ่งนี้ค่อนข้างง่ายที่จะทําและให้ความยืดหยุ่นกับคุณมาก
เมื่อคุณสร้างตารางวันที่ใน Excel คุณจะเริ่มต้นด้วยคอลัมน์เดียวที่มีช่วงวันที่ที่ต่อเนื่องกัน จากนั้นคุณสามารถสร้างคอลัมน์เพิ่มเติม เช่น ปี ไตรมาส เดือน ปีงบประมาณ ช่วงเวลา เป็นต้น ในเวิร์กชีต Excel โดยใช้สูตร Excel หรือหลังจากที่คุณคัดลอกตารางลงในตัวแบบข้อมูล แล้ว คุณสามารถสร้างเป็นคอลัมน์จากการคํานวณได้ การสร้างคอลัมน์วันที่เพิ่มเติมใน Power Pivot ได้อธิบายไว้ในส่วน การเพิ่มคอลัมน์วันที่ใหม่ลงในตารางวันที่ ต่อไปในบทความนี้
วิธีการ: สร้างตารางวันที่ใน Excel และคัดลอกลงในตัวแบบข้อมูล
-
ใน Excel ในเวิร์กชีตเปล่า ในเซลล์ A1 ให้พิมพ์ชื่อส่วนหัวของคอลัมน์เพื่อระบุช่วงของวันที่ โดยทั่วไปแล้ว จะมีลักษณะเช่น Date, DateTime หรือ DateKey
-
ในเซลล์ A2 ให้พิมพ์วันที่เริ่มต้น ตัวอย่างเช่น 1/1/2010
-
คลิกจุดจับเติม แล้วลากลงไปยังหมายเลขแถวที่มีวันที่สิ้นสุด ตัวอย่างเช่น 12/31/2016
-
เลือกแถวทั้งหมดในคอลัมน์ วันที่ (รวมถึงชื่อส่วนหัวในเซลล์ A1)
-
ในกลุ่ม สไตล์ ให้คลิก จัดรูปแบบเป็นตาราง แล้วเลือกสไตล์
-
ในกล่องโต้ตอบ จัดรูปแบบเป็นตาราง ให้คลิก ตกลง
-
คัดลอกแถวทั้งหมด รวมถึงส่วนหัว
-
ใน Power Pivot บนแท็บ หน้าแรก ให้คลิก วาง
-
ใน วางการแสดงตัวอย่าง > ชื่อตาราง ให้พิมพ์ชื่อ เช่น วันที่ หรือ ปฏิทิน ปล่อยให้ ใช้แถวแรกเป็นส่วนหัวของคอลัมน์ ถูกเลือกไว้ แล้วคลิก ตกลง
ตารางวันที่ใหม่ (มีชื่อว่า ปฏิทิน ในตัวอย่างนี้) ใน Power Pivot จะมีลักษณะดังนี้:
หมายเหตุ: คุณยังสามารถสร้างตารางที่ลิงก์โดยใช้ เพิ่มไปยังตัวแบบข้อมูล อย่างไรก็ตาม การทําเช่นนี้จะทําให้เวิร์กบุ๊กของคุณมีขนาดใหญ่โดยไม่จําเป็น เนื่องจากเวิร์กบุ๊กมีตารางวันที่สองเวอร์ชัน ใน Excel และอีกหนึ่งรายการใน Power Pivot
หมายเหตุ: วันที่ของชื่อเป็นคําสําคัญใน Power Pivot ถ้าคุณตั้งชื่อตารางที่คุณสร้างในวันที่ Power Pivot คุณจะต้องใส่เครื่องหมายอัญประกาศเดี่ยวในสูตร DAX ที่อ้างอิงตารางนั้นในอาร์กิวเมนต์ รูปภาพและสูตรตัวอย่างทั้งหมดในบทความนี้อ้างอิงตารางวันที่ที่สร้างขึ้นใน Power Pivot ที่มีชื่อว่า ปฏิทิน
ตอนนี้คุณมีตารางวันที่ในตัวแบบข้อมูลของคุณ คุณสามารถเพิ่มคอลัมน์วันที่ใหม่ เช่น ปี เดือน เป็นต้น โดยใช้ DAX
การเพิ่มคอลัมน์วันที่ใหม่ลงในตารางวันที่
ตารางวันที่ที่มีคอลัมน์วันที่เดียวที่มีหนึ่งแถวสําหรับทุกวันของแต่ละปีเป็นสิ่งสําคัญสําหรับการกําหนดวันที่ทั้งหมดในช่วงวันที่ นอกจากนี้ยังจําเป็นสําหรับการสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงและตารางวันที่ แต่คอลัมน์วันที่เดี่ยวที่มีหนึ่งแถวสําหรับทุกวันจะไม่มีประโยชน์เมื่อทําการวิเคราะห์ตามวันที่ในรายงาน PivotTable หรือ Power View คุณต้องการให้ตารางวันที่ของคุณรวมคอลัมน์ที่ช่วยให้คุณรวมข้อมูลของคุณสําหรับช่วงหรือกลุ่มวันที่ ตัวอย่างเช่น คุณอาจต้องการรวมยอดขายตามเดือนหรือไตรมาส หรือคุณอาจสร้างการวัดที่คํานวณการเติบโตของปีต่อปี ในแต่ละกรณี ตารางวันที่ของคุณจําเป็นต้องมีคอลัมน์แบบปี เดือน หรือไตรมาสที่ช่วยให้คุณสามารถรวมข้อมูลของคุณสําหรับช่วงเวลานั้นได้
ถ้าคุณนําเข้าตารางวันที่ของคุณจากแหล่งข้อมูลที่สัมพันธ์กัน ตารางวันที่อาจมีคอลัมน์วันที่ชนิดต่างๆ ที่คุณต้องการอยู่แล้ว ในบางกรณี คุณอาจต้องการปรับเปลี่ยนคอลัมน์เหล่านั้นบางคอลัมน์หรือสร้างคอลัมน์วันที่เพิ่มเติม โดยเฉพาะอย่างยิ่ง ถ้าคุณสร้างตารางวันที่ของคุณเองใน Excel และคัดลอกลงในตัวแบบข้อมูล โชคดีที่การสร้างคอลัมน์วันที่ใหม่ใน Power Pivot นั้นค่อนข้างง่ายด้วย ฟังก์ชันวันที่และเวลา ใน DAX
เคล็ดลับ: ถ้าคุณยังไม่ได้ทํางานกับ DAX จุดเริ่มต้นการเรียนรู้ที่ยอดเยี่ยมคือการเริ่มต้นใช้งานด่วน: เรียนรู้ข้อมูลพื้นฐานของ DAX ใน 30 นาที Office.com
ฟังก์ชันวันที่และเวลา DAX
ถ้าคุณเคยทํางานกับฟังก์ชันวันที่และเวลาในสูตร Excel คุณอาจคุ้นเคยกับฟังก์ชันวันที่และเวลา แม้ว่าฟังก์ชันเหล่านี้จะคล้ายกับฟังก์ชันใน Excel แต่มีความแตกต่างที่สําคัญบางประการ:
-
ฟังก์ชันวันที่และเวลา DAX ใช้ชนิดข้อมูลวันที่และเวลา
-
ซึ่งสามารถใช้ค่าจากคอลัมน์เป็นอาร์กิวเมนต์ได้
-
ซึ่งสามารถใช้เพื่อส่งกลับ และ/หรือจัดการค่าวันที่
ฟังก์ชันเหล่านี้มักจะใช้เมื่อสร้างคอลัมน์วันที่แบบกําหนดเองในตารางวันที่ ดังนั้นจึงเป็นสิ่งสําคัญที่จะต้องทําความเข้าใจ เราจะใช้ฟังก์ชันเหล่านี้เพื่อสร้างคอลัมน์สําหรับ Year, Quarter, FiscalMonth และอื่นๆ
หมายเหตุ: ฟังก์ชันวันที่และเวลาใน DAX ไม่เหมือนกับฟังก์ชันตัวแสดงเวลา เรียนรู้เพิ่มเติมเกี่ยวกับตัวแสดงเวลาใน Power Pivot ใน Excel
DAX มีฟังก์ชันวันที่และเวลาต่อไปนี้:
มีฟังก์ชัน DAX อื่นๆ อีกมากมายที่คุณสามารถใช้ในสูตรของคุณได้เช่นกัน ตัวอย่างเช่น สูตรจํานวนมากที่อธิบายไว้ที่นี่ใช้ ฟังก์ชันทางคณิตศาสตร์และตรีโกณมิติ เช่น MOD และ TRUNCฟังก์ชันทางตรรกะ เช่น ฟังก์ชัน IF และ ฟังก์ชันข้อความ เช่น FORMAT สําหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน DAX อื่นๆ ให้ดูที่ส่วน แหล่งข้อมูลเพิ่มเติม ต่อไปในบทความนี้
ตัวอย่างสูตรสําหรับปีปฏิทิน
ตัวอย่างต่อไปนี้อธิบายสูตรที่ใช้ในการสร้างคอลัมน์เพิ่มเติมในตารางวันที่ที่ชื่อว่า ปฏิทิน มีคอลัมน์หนึ่งที่มีชื่อว่า วันที่ อยู่แล้ว และมีช่วงวันที่ที่ติดกันตั้งแต่วันที่ 1/1/2553 ถึง 31/12/2559
ปี
=YEAR([วันที่])
ในสูตรนี้ ฟังก์ชัน YEAR จะส่งกลับปีจากค่าในคอลัมน์ Date เนื่องจากค่าในคอลัมน์ วันที่ เป็นชนิดข้อมูลเวลา วันที่ ฟังก์ชัน YEAR จึงรู้วิธีส่งกลับปีจากชนิดข้อมูลนั้น
เดือน
=MONTH([date])
ในสูตรนี้ เหมือนกับฟังก์ชัน YEAR เราสามารถใช้ฟังก์ชัน MONTH เพื่อส่งกลับค่าเดือนจากคอลัมน์ วันที่
ไตรมาส
=INT(([Month]+2)/3)
ในสูตรนี้ เราใช้ฟังก์ชัน INT เพื่อส่งกลับค่าวันที่เป็นจํานวนเต็ม อาร์กิวเมนต์ที่เราระบุสําหรับฟังก์ชัน INT คือค่าจากคอลัมน์ เดือน บวก 2 แล้วหารด้วย 3 เพื่อให้ได้ไตรมาสของเรา 1 ถึง 4
ชื่อเดือน
=FORMAT([วันที่],"mmmm")
ในสูตรนี้ เมื่อต้องการรับชื่อเดือน เราใช้ฟังก์ชัน FORMAT เพื่อแปลงค่าตัวเลขจากคอลัมน์วันที่เป็นข้อความ เราระบุคอลัมน์ วันที่ เป็นอาร์กิวเมนต์แรก แล้วตามด้วยรูปแบบ เราต้องการให้ชื่อเดือนของเราแสดงอักขระทั้งหมด ดังนั้นเราจึงใช้ "mmmm" ผลลัพธ์ของเรามีลักษณะดังนี้:
ถ้าเราต้องการส่งกลับชื่อเดือนแบบย่อเป็นตัวอักษรสามตัว เราจะใช้ "mmm" ในอาร์กิวเมนต์รูปแบบ
วันของสัปดาห์
=FORMAT([วันที่],"ddd")
ในสูตรนี้ เราใช้ฟังก์ชัน FORMAT เพื่อรับชื่อวัน เนื่องจากเราต้องการชื่อวันที่แบบย่อ เราจึงระบุ "ddd" ในอาร์กิวเมนต์รูปแบบ
PivotTable ตัวอย่าง
เมื่อคุณมีเขตข้อมูลสําหรับวันที่ เช่น ปี ไตรมาส เดือน เป็นต้น คุณสามารถใช้เขตข้อมูลเหล่านั้นใน PivotTable หรือรายงานได้ ตัวอย่างเช่น รูปต่อไปนี้แสดงเขตข้อมูล SalesAmount จากตาราง ข้อมูลยอดขาย ใน VALUES และ Year และ Quarter จากตารางมิติปฏิทินใน ROWS SalesAmount จะถูกรวมสําหรับบริบทปีและไตรมาส
ตัวอย่างสูตรสําหรับปีงบประมาณ
ปีงบประมาณ
=IF([เดือน]<= 6,[ปี],[ปี]+1)
ในตัวอย่างนี้ ปีบัญชีเริ่มต้นในวันที่ 1 กรกฎาคม
ไม่มีฟังก์ชันที่สามารถแยกปีงบประมาณออกจากค่าวันที่ได้ เนื่องจากวันที่เริ่มต้นและสิ้นสุดสําหรับปีงบประมาณมักจะแตกต่างจากปีปฏิทิน เมื่อต้องการรับปีงบประมาณ ก่อนอื่นเราจะใช้ฟังก์ชัน IF เพื่อทดสอบว่าค่าสําหรับเดือนน้อยกว่าหรือเท่ากับ 6 ในอาร์กิวเมนต์ที่สอง ถ้าค่าสําหรับ Month น้อยกว่าหรือเท่ากับ 6 ให้ส่งกลับค่าจากคอลัมน์ Year ถ้าไม่ ให้ส่งกลับค่าจาก Year แล้วเพิ่ม 1
อีกวิธีหนึ่งในการระบุค่าสิ้นเดือนของปีงบประมาณคือการสร้างการวัดที่เพียงแค่ระบุเดือนเท่านั้น ตัวอย่างเช่น FYE:=6 คุณสามารถอ้างอิงชื่อหน่วยวัดแทนหมายเลขเดือนได้ ตัวอย่างเช่น =IF([Month]<=[FYE],[Year],[Year]+1) ซึ่งจะมีความยืดหยุ่นมากขึ้นเมื่ออ้างอิงถึงสิ้นเดือนของปีงบประมาณในสูตรที่แตกต่างกันหลายสูตร
เดือนงบประมาณ
=IF([Month]<= 6, 6+[Month], [Month]- 6)
ในสูตรนี้ เราระบุว่าค่าสําหรับ [Month] น้อยกว่าหรือเท่ากับ 6 แล้วเอา 6 และบวกค่าจากเดือน มิฉะนั้นให้ลบ 6 ออกจากค่าจาก [Month]
ไตรมาสงบประมาณ
=INT(([FiscalMonth]+2)/3)
สูตรที่เราใช้สําหรับ FiscalQuarter นั้นเหมือนกับของไตรมาสในปีปฏิทินของเรา ความแตกต่างเพียงอย่างเดียวคือเราระบุ [FiscalMonth] แทน [เดือน]
วันหยุดหรือวันพิเศษ
คุณอาจต้องการรวมคอลัมน์วันที่ที่ระบุวันที่เป็นวันหยุดหรือวันที่พิเศษอื่นๆ ตัวอย่างเช่น คุณอาจต้องการหาผลรวมยอดขายของวันปีใหม่ด้วยการเพิ่มเขตข้อมูลวันหยุดลงใน PivotTable เป็นตัวแบ่งส่วนข้อมูล หรือกรอง ในกรณีอื่นๆ คุณอาจต้องการแยกวันที่เหล่านั้นออกจากคอลัมน์วันที่อื่นหรือในหน่วยวัด
รวมถึงวันหยุดหรือวันพิเศษนั้นค่อนข้างง่าย คุณสามารถสร้างตารางใน Excel ที่มีวันที่ที่คุณต้องการรวมได้ จากนั้นคุณสามารถคัดลอกหรือใช้ เพิ่มลงในตัวแบบข้อมูล เพื่อเพิ่มลงในตัวแบบข้อมูลเป็นตารางที่ลิงก์ได้ ในกรณีส่วนใหญ่ ไม่จําเป็นต้องสร้างความสัมพันธ์ระหว่างตารางและตารางปฏิทิน สูตรใดๆ ที่อ้างอิงถึงสูตรนั้นสามารถใช้ฟังก์ชัน 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 |
คริสต์มาส |
ในตารางวันที่ เราสร้างคอลัมน์ที่ชื่อ Holiday และใช้สูตรดังนี้:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
มาดูสูตรนี้ให้ละเอียดกว่านี้
เราใช้ฟังก์ชัน LOOKUPVALUE เพื่อรับค่าจากคอลัมน์ วันหยุด ในตาราง วันหยุด ในอาร์กิวเมนต์แรก เราระบุคอลัมน์ที่ค่าผลลัพธ์ของเราจะอยู่ เราระบุคอลัมน์ วันหยุด ในตาราง วันหยุด เนื่องจากนั่นคือค่าที่เราต้องการส่งกลับ
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
จากนั้นเราจะระบุอาร์กิวเมนต์ที่สอง คอลัมน์การค้นหาที่มีวันที่ที่เราต้องการค้นหา เราระบุคอลัมน์ วันที่ ในตาราง วันหยุด ดังนี้:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
สุดท้าย เราระบุคอลัมน์ในตารางปฏิทินของเราที่มีวันที่ที่เราต้องการค้นหาในตารางวันหยุด แน่นอนว่านี่จะเป็นคอลัมน์ วันที่ ในตารางปฏิทิน
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
คอลัมน์ วันหยุด จะส่งกลับชื่อวันหยุดสําหรับแต่ละแถวที่มีค่าวันที่ที่ตรงกับวันที่ในตารางวันหยุด
ปฏิทินแบบกําหนดเอง - ช่วงเวลาสี่สัปดาห์สิบสามรอบ
บางองค์กร เช่น ร้านค้าปลีกหรือบริการอาหาร มักจะรายงานช่วงเวลาต่างๆ เช่น ช่วงเวลาสิบสามสัปดาห์ ด้วยปฏิทินระยะเวลาสี่สัปดาห์สิบสี่สัปดาห์ แต่ละช่วงเวลาคือ 28 วัน ดังนั้น แต่ละรอบระยะเวลาจะมีสี่วันจันทร์ สี่วันอังคาร สี่วันพุธ และอื่นๆ แต่ละช่วงเวลามีจํานวนวันเท่ากัน และโดยปกติวันหยุดจะอยู่ภายในช่วงเวลาเดียวกันในแต่ละปี คุณสามารถเลือกที่จะเริ่มต้นรอบระยะเวลาในวันใดก็ได้ของสัปดาห์ เช่นเดียวกับวันที่ในปฏิทินหรือปีงบประมาณ คุณสามารถใช้ DAX เพื่อสร้างคอลัมน์เพิ่มเติมที่มีวันที่แบบกําหนดเอง
ในตัวอย่างด้านล่าง รอบระยะเวลาเต็มแรกจะเริ่มต้นในวันอาทิตย์แรกของปีงบประมาณ ในกรณีนี้ ปีบัญชีเริ่มต้นในวันที่ 17/7/2019
สัปดาห์
ค่านี้ทําให้เรามีจํานวนสัปดาห์ที่เริ่มต้นด้วยสัปดาห์เต็มสัปดาห์แรกในปีงบประมาณ ในตัวอย่างนี้ สัปดาห์เต็มสัปดาห์แรกจะเริ่มต้นในวันอาทิตย์ ดังนั้นสัปดาห์แรกแบบเต็มสัปดาห์ในปีงบประมาณแรกในตารางปฏิทินจะเริ่มต้นในวันที่ 4/7/2010 และดําเนินต่อตลอดทั้งสัปดาห์ที่ผ่านมาในตารางปฏิทิน ในขณะที่ค่านี้เองไม่ใช่สิ่งที่มีประโยชน์ในการวิเคราะห์ แต่จําเป็นต้องคํานวณเพื่อใช้ในสูตรช่วงเวลา 28 วันอื่น ๆ
=INT([date]-40356)/7)
มาดูสูตรนี้ให้ละเอียดกว่านี้
ขั้นแรก เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์ วันที่ เป็นจํานวนเต็ม ดังนี้
=INT([วันที่])
จากนั้นเราต้องการค้นหาวันอาทิตย์แรกในปีงบประมาณแรก เราเห็นว่าวันที่ 4/7/2553
ตอนนี้ ลบ 40356 (ซึ่งเป็นจํานวนเต็มสําหรับ 6/27/2010 ซึ่งเป็นวันอาทิตย์สุดท้ายจากปีงบประมาณก่อนหน้า) จากค่านั้นเพื่อรับจํานวนวันตั้งแต่วันเริ่มต้นในตารางปฏิทินของเรา ดังนี้:
=INT([date]-40356)
จากนั้นหารผลลัพธ์ด้วย 7 (วันในหนึ่งสัปดาห์) ดังนี้
=INT(([date]-40356)/7)
ผลลัพธ์มีลักษณะดังนี้:
เครื่องหมายมหัพภาค
ช่วงเวลาในปฏิทินแบบกําหนดเองนี้มี 28 วัน และจะเริ่มต้นในวันอาทิตย์เสมอ คอลัมน์นี้จะส่งกลับหมายเลขของรอบระยะเวลาที่เริ่มต้นด้วยวันอาทิตย์แรกในปีงบประมาณแรก
=INT(([Week]+3)/4)
มาดูสูตรนี้ให้ละเอียดกว่านี้
อันดับแรก เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์ สัปดาห์ เป็นจํานวนเต็ม ดังนี้
=INT([Week])
จากนั้นเพิ่ม 3 ลงในค่านั้น ดังนี้:
=INT([Week]+3)
จากนั้นหารผลลัพธ์ด้วย 4 ดังนี้
=INT(([Week]+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([Period],13), MOD([Period],13),13)
สูตรนี้มีความซับซ้อนมากขึ้นเล็กน้อยดังนั้นเราจะอธิบายก่อนในภาษาที่เราเข้าใจได้ดีขึ้น สูตรนี้ระบุว่า ให้หารค่าจาก [เครื่องหมายมหัพภาค] ด้วย 13 เพื่อหาตัวเลขมหัพภาค (1-13) ในปี ถ้าตัวเลขนั้นเป็น 0 ให้ส่งกลับ 13
ขั้นแรก เราจะสร้างสูตรที่ส่งกลับค่าที่เหลือจากมหัพภาคคูณ 13 เราสามารถใช้ MOD (ฟังก์ชันคณิตศาสตร์และตรีโกณมิติ) เช่นนี้:
=MOD([Period],13)
ซึ่งส่วนใหญ่แล้ว จะให้ผลลัพธ์ที่เราต้องการ แก่เรา ยกเว้นว่าค่าสําหรับ คาบเวลา เป็น 0 เนื่องจากวันที่เหล่านั้นไม่ได้อยู่ภายในปีงบประมาณแรก เหมือนกับในห้าวันแรกของตัวอย่างตารางวันที่ในปฏิทินของเรา เราสามารถจัดการเรื่องนี้ได้ด้วยฟังก์ชัน IF ในกรณีที่ผลลัพธ์ของเราคือ 0 เราส่งคืน 13 เช่นนี้:
=IF(MOD([Period],13),MOD([Period],13),13)
ผลลัพธ์มีลักษณะดังนี้:
PivotTable ตัวอย่าง
รูปด้านล่างแสดง PivotTable ที่มีเขตข้อมูล SalesAmount จากตาราง ข้อเท็จจริงยอดขาย ใน VALUES และเขตข้อมูล PeriodFiscalYear และ PeriodInFiscalYear จากตารางมิติวันที่ปฏิทินใน ROWS SalesAmount จะถูกรวมสําหรับบริบทตามปีบัญชีและรอบระยะเวลา 28 วันในปีบัญชี
ความสัมพันธ์
หลังจากที่คุณได้สร้างตารางวันที่ในตัวแบบข้อมูลเพื่อเริ่มเรียกดูข้อมูลของคุณใน PivotTable และรายงาน และเพื่อรวมข้อมูลโดยยึดตามคอลัมน์ในตารางมิติวันที่ของคุณ คุณจําเป็นต้องสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงกับข้อมูลธุรกรรมของคุณและตารางวันที่
เนื่องจากคุณจําเป็นต้องสร้างความสัมพันธ์โดยยึดตามวันที่ คุณจะต้องตรวจสอบให้แน่ใจว่าคุณสร้างความสัมพันธ์นั้นระหว่างคอลัมน์ที่มีค่าเป็นชนิดข้อมูล datetime (Date)
สําหรับค่าวันที่ทั้งหมดในตารางข้อเท็จจริง คอลัมน์การค้นหาที่เกี่ยวข้องในตารางวันที่ต้องมีค่าที่ตรงกัน ตัวอย่างเช่น แถว (ระเบียนทรานแซคชัน) ในตาราง ข้อมูลยอดขาย ที่มีค่า 15/8/2555 12:00 AM ในคอลัมน์ DateKey ต้องมีค่าที่สอดคล้องกันในคอลัมน์ วันที่ ที่เกี่ยวข้องในตารางวันที่ (ชื่อ ปฏิทิน) นี่คือหนึ่งในเหตุผลที่สําคัญที่สุดที่คุณต้องการให้คอลัมน์วันที่ของคุณในตารางวันที่มีช่วงวันที่ที่อยู่ติดกันซึ่งมีวันที่ที่เป็นไปได้ในตารางข้อเท็จจริงของคุณ
หมายเหตุ: ในขณะที่คอลัมน์วันที่ในแต่ละตารางจะต้องเป็นชนิดข้อมูลเดียวกัน (วันที่) แต่รูปแบบของแต่ละคอลัมน์จะไม่สําคัญ
หมายเหตุ: ถ้า Power Pivot ไม่อนุญาตให้คุณสร้างความสัมพันธ์ระหว่างสองตารางเขตข้อมูลวันที่อาจไม่เก็บวันที่และเวลาในระดับความแม่นยําเท่ากัน ค่าอาจมีลักษณะเหมือนกัน ทั้งนี้ขึ้นอยู่กับการจัดรูปแบบคอลัมน์ แต่จะถูกเก็บไว้ต่างกัน อ่านเพิ่มเติมเกี่ยวกับการทํางานกับเวลา
หมายเหตุ: หลีกเลี่ยงการใช้แป้นตัวแทนจํานวนเต็มในความสัมพันธ์ เมื่อคุณนําเข้าข้อมูลจากแหล่งข้อมูลที่สัมพันธ์กัน คอลัมน์วันที่และเวลามักจะแสดงด้วยคีย์ตัวแทน ซึ่งเป็นคอลัมน์จํานวนเต็มที่ใช้แทนวันที่ที่ไม่ซ้ํากัน ใน Power Pivot คุณควรหลีกเลี่ยงการสร้างความสัมพันธ์โดยใช้แป้นวันที่/เวลาจํานวนเต็ม และใช้คอลัมน์ที่มีค่าที่ไม่ซ้ํากันกับชนิดข้อมูลวันที่แทน แม้ว่าการใช้คีย์ตัวแทนถือเป็นแนวทางปฏิบัติที่ดีที่สุดในคลังสินค้าข้อมูลแบบดั้งเดิม แต่คีย์จํานวนเต็มจะไม่จําเป็นใน Power Pivot และอาจทําให้ยากต่อการจัดกลุ่มค่าใน PivotTable ตามช่วงเวลาวันที่ที่แตกต่างกัน
ถ้าคุณได้รับข้อผิดพลาดชนิดไม่ตรงกันเมื่อพยายามสร้างความสัมพันธ์ เป็นไปได้ว่าคอลัมน์ในตารางข้อเท็จจริงไม่ใช่ชนิดข้อมูลวันที่ ปัญหานี้อาจเกิดขึ้นได้เมื่อ Power Pivot ไม่สามารถแปลงข้อมูลที่ไม่ใช่วันที่โดยอัตโนมัติ (โดยปกติจะเป็นชนิดข้อมูลข้อความ) ให้เป็นชนิดข้อมูลวันที่ได้ คุณยังคงสามารถใช้คอลัมน์ในตารางข้อเท็จจริงของคุณได้ แต่คุณจะต้องแปลงข้อมูลด้วยสูตร DAX ในคอลัมน์จากการคํานวณใหม่ ดู การแปลงวันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่ ในภายหลังในภาคผนวก
หลายความสัมพันธ์
ในบางกรณี อาจจําเป็นต้องสร้างหลายความสัมพันธ์หรือสร้างตารางวันที่หลายตาราง ตัวอย่างเช่น ถ้ามีเขตข้อมูลวันที่หลายเขตข้อมูลในตาราง ข้อมูลยอดขาย เช่น DateKey, ShipDate และ ReturnDate เขตข้อมูลเหล่านั้นสามารถมีความสัมพันธ์กับเขตข้อมูล วันที่ ในตารางวันที่ในปฏิทิน ได้ แต่มีเพียงหนึ่งเขตข้อมูลเท่านั้นที่สามารถเป็นความสัมพันธ์ที่ใช้งานอยู่ได้ ในกรณีนี้ เนื่องจาก DateKey จะแสดงวันที่ของทรานแซคชัน ดังนั้นวันที่ที่สําคัญที่สุด จึงควรเป็นความสัมพันธ์ที่ใช้งานอยู่ ผู้อื่นมีความสัมพันธ์ที่ไม่ได้ใช้งาน
PivotTable ต่อไปนี้จะคํานวณยอดขายรวมตามปีงบประมาณและไตรมาสงบประมาณ หน่วยวัดที่ชื่อ Total Sales ที่มีสูตร Total Sales:=SUM([SalesAmount]) จะถูกวางไว้ในเขตข้อมูล VALUES และ FiscalYear และ FiscalQuarter จากตารางวันที่ของปฏิทินจะถูกวางในแถว
PivotTable แบบไปข้างหน้านี้ทํางานอย่างถูกต้องเนื่องจากเราต้องการรวมยอดขายทั้งหมดของเราตามวันที่ทรานแซคชันใน DateKey การวัดยอดขายรวมของเราใช้วันที่ใน DateKey และจะถูกรวมโดยปีงบประมาณและไตรมาสงบประมาณ เนื่องจากมีความสัมพันธ์ระหว่าง DateKey ในตารางยอดขายและคอลัมน์วันที่ในตารางวันที่ในปฏิทิน
ความสัมพันธ์ที่ไม่ได้ใช้งาน
แต่ถ้าเราต้องการรวมยอดขายทั้งหมดของเราไม่ใช่ตามวันที่ทําธุรกรรม แต่ตาม วันที่จัดส่ง? เราต้องการความสัมพันธ์ระหว่างคอลัมน์ ShipDate ในตารางยอดขายและคอลัมน์ วันที่ ในตารางปฏิทิน หากเราไม่สร้างความสัมพันธ์ดังกล่าว การรวมของเราจะยึดตามวันที่ทําธุรกรรมเสมอ อย่างไรก็ตาม เราสามารถมีความสัมพันธ์ได้หลายความสัมพันธ์ แม้ว่าจะมีเพียงความสัมพันธ์เดียวเท่านั้นที่สามารถใช้งานอยู่ได้ และเนื่องจากวันที่ธุรกรรมมีความสําคัญมากที่สุด จึงได้รับความสัมพันธ์ที่ใช้งานอยู่กับตารางปฏิทิน
ในกรณีนี้ ShipDate มีความสัมพันธ์ที่ไม่ได้ใช้งาน ดังนั้นสูตรการวัดใดๆ ที่สร้างขึ้นเพื่อรวมข้อมูลโดยยึดตามวันที่จัดส่งต้องระบุความสัมพันธ์ที่ไม่ได้ใช้งานโดยใช้ฟังก์ชัน USERELATIONSHIP
ตัวอย่างเช่น เนื่องจากมีความสัมพันธ์ที่ไม่ได้ใช้งานระหว่างคอลัมน์ ShipDate ในตารางยอดขายและคอลัมน์ วันที่ ในตารางปฏิทิน เราจึงสามารถสร้างการวัดที่รวมยอดขายทั้งหมดตามวันที่จัดส่งได้ เราใช้สูตรเช่นนี้เพื่อระบุความสัมพันธ์ที่จะใช้:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
สูตรนี้เพียงแค่ระบุ: คํานวณผลรวมสําหรับ SalesAmount แต่กรองโดยใช้ความสัมพันธ์ระหว่างคอลัมน์ วันที่จัดส่ง ในตาราง ยอดขาย และคอลัมน์ วันที่ ในตารางปฏิทิน
ในตอนนี้ ถ้าเราสร้าง PivotTable และใส่หน่วยวัด Total Sales by Ship Date ใน VALUES และปีงบประมาณและไตรมาสงบประมาณใน ROWS เราจะเห็นผลรวมทั้งหมดเดียวกัน แต่ผลรวมอื่นๆ ทั้งหมดสําหรับปีบัญชีและไตรมาสงบประมาณแตกต่างกัน เนื่องจากจะยึดตามวันที่จัดส่งไม่ใช่วันที่ธุรกรรม
การใช้ความสัมพันธ์ที่ไม่ได้ใช้งานช่วยให้คุณสามารถใช้ตารางวันที่ได้เพียงตารางเดียว เท่านั้น แต่ต้องวัด (เช่น ยอดขายรวมตามวันที่จัดส่ง) อ้างอิงความสัมพันธ์ที่ไม่ได้ใช้งานในสูตรของตารางนั้น มีอีกทางเลือกหนึ่งคือใช้ตารางวันที่หลายตาราง
ตารางวันที่หลายตาราง
อีกวิธีหนึ่งในการทํางานกับคอลัมน์วันที่หลายคอลัมน์ในตารางข้อเท็จจริงของคุณคือการสร้างตารางวันที่หลายตาราง และสร้างความสัมพันธ์ที่ใช้งานแยกต่างหากระหว่างคอลัมน์เหล่านั้น มาดูที่ตัวอย่างตารางยอดขายของเราอีกครั้ง เรามีสามคอลัมน์ที่มีวันที่ซึ่งเราอาจต้องการรวบรวมข้อมูลเกี่ยวกับ:
-
DateKey ที่มีวันที่ขายสําหรับแต่ละธุรกรรม
-
วันที่จัดส่ง – พร้อมวันที่และเวลาที่สินค้าที่ขายถูกจัดส่งไปยังลูกค้า
-
วันที่ส่งกลับ ที่มีวันที่และเวลาเมื่อได้รับรายการอย่างน้อยหนึ่งรายการ
โปรดทราบว่าฟิลด์ DateKey ที่มีวันที่ธุรกรรมมีความสําคัญมากที่สุด เราจะทําการรวบรวมส่วนใหญ่ของเราตามวันที่เหล่านี้ ดังนั้นเราจะต้องการความสัมพันธ์ระหว่างข้อมูลและคอลัมน์ วันที่ ในตารางปฏิทินอย่างแน่นอน ถ้าเราไม่ต้องการสร้างความสัมพันธ์ที่ไม่ได้ใช้งานระหว่าง ShipDate และวันที่ส่งคืน และเขตข้อมูล วันที่ ในตารางปฏิทิน ดังนั้น การกําหนดให้ต้องใช้สูตรการวัดพิเศษ เราสามารถสร้างตารางวันที่เพิ่มเติมสําหรับวันที่จัดส่งและวันที่ส่งคืนได้ จากนั้นเราสามารถสร้างความสัมพันธ์ที่ใช้งานได้ระหว่างพวกเขา
ในตัวอย่างนี้ เราได้สร้างตารางวันที่อื่นที่ชื่อ ShipCalendar ซึ่งแน่นอนว่ายังหมายถึงการสร้างคอลัมน์วันที่เพิ่มเติม และเนื่องจากคอลัมน์วันที่เหล่านี้อยู่ในตารางวันที่อื่น เราจึงต้องการตั้งชื่อคอลัมน์ในลักษณะที่ทําให้คอลัมน์เหล่านั้นแตกต่างจากคอลัมน์เดียวกันในตารางปฏิทิน ตัวอย่างเช่น เราได้สร้างคอลัมน์ที่ชื่อ ShipYear, ShipMonth, ShipQuarter และอื่นๆ
ถ้าเราสร้าง PivotTable และใส่หน่วยวัด Total Sales ของเราใน VALUES และ ShipFiscalYear และ ShipFiscalQuarter ใน ROWS เราจะเห็นผลลัพธ์เดียวกันกับที่เราเห็นเมื่อเราสร้างความสัมพันธ์ที่ไม่ได้ใช้งานและเขตข้อมูลจากการคํานวณ Total Sales by Ship Date แบบพิเศษ
แต่ละวิธีเหล่านี้ต้องได้รับการพิจารณาอย่างรอบคอบ เมื่อใช้ความสัมพันธ์หลายความสัมพันธ์กับตารางวันที่เดียว คุณอาจต้องสร้างการวัดพิเศษที่ส่งต่อความสัมพันธ์ที่ไม่ได้ใช้งานโดยใช้ฟังก์ชัน USERELATIONSHIP ในทางตรงกันข้าม การสร้างตารางวันที่หลายตารางอาจสร้างความสับสนในรายการเขตข้อมูลได้ และเนื่องจากคุณมีตารางเพิ่มเติมในตัวแบบข้อมูล จึงต้องใช้หน่วยความจํามากขึ้น ทดลองว่าแบบไหนดีที่สุดสําหรับคุณ
คุณสมบัติตารางวันที่
คุณสมบัติ ตารางวันที่ จะตั้งค่า Metadata ที่จําเป็นสําหรับฟังก์ชัน Time-Intelligence เช่น TOTALYTD, PREVIOUSMONTH และ DATESBETWEEN เพื่อให้ทํางานได้อย่างถูกต้อง เมื่อเรียกใช้การคํานวณโดยใช้หนึ่งในฟังก์ชันเหล่านี้ โปรแกรมสูตรของ Power Pivot จะทราบตําแหน่งที่จะหาวันที่ที่ต้องการ
คำเตือน: ถ้าไม่ได้ตั้งค่าคุณสมบัตินี้ การวัดโดยใช้ฟังก์ชัน Time-Intelligence DAX อาจไม่ส่งกลับผลลัพธ์ที่ถูกต้อง
เมื่อคุณตั้งค่าคุณสมบัติ ตารางวันที่ คุณจะระบุตารางวันที่และคอลัมน์วันที่ของชนิดข้อมูล วันที่ (เวลา) ในนั้น
วิธีการ: ตั้งค่าคุณสมบัติ ตารางวันที่
-
ในหน้าต่าง PowerPivot ให้เลือกตาราง ปฏิทิน
-
บนแท็บ ออกแบบ ให้คลิก ทําเครื่องหมายเป็นตารางวันที่
-
ในกล่องโต้ตอบ ทําเครื่องหมายเป็นตารางวันที่ ให้เลือกคอลัมน์ที่มีค่าที่ไม่ซ้ํากันและชนิดข้อมูล วันที่
การทํางานกับเวลา
ค่าวันที่ทั้งหมดที่มีชนิดข้อมูลวันที่ใน Excel หรือ SQL Server คือตัวเลข ที่รวมอยู่ในตัวเลขนั้นคือตัวเลขที่อ้างอิงถึงเวลา ในหลายกรณีเวลานั้นสําหรับแต่ละแถวและทุกแถวคือเที่ยงคืน ตัวอย่างเช่น ถ้าเขตข้อมูล DateTimeKey ในตารางข้อมูลการขายมีค่า เช่น 19/10/2553 12:00:00 น. ซึ่งหมายความว่าค่าจะมีระดับความแม่นยําเป็นวัน ถ้าค่าเขตข้อมูล DateTimeKey มีเวลารวมอยู่ ตัวอย่างเช่น 19/10/2553 8:44:00 น. นั่นหมายความว่าค่าอยู่ในระดับนาทีของความแม่นยํา ค่าอาจเป็นความแม่นยําของระดับชั่วโมง หรือแม้แต่วินาทีของความแม่นยํา ระดับของความแม่นยําในค่าเวลาจะมีผลกระทบอย่างมากต่อวิธีที่คุณสร้างตารางวันที่ของคุณและความสัมพันธ์ระหว่างตารางกับตารางข้อเท็จจริงของคุณ
คุณจําเป็นต้องตรวจสอบว่าคุณจะรวมข้อมูลของคุณเป็นระดับความแม่นยําวันหรือในระดับเวลาที่มีความแม่นยํา กล่าวคือ คุณอาจต้องการใช้คอลัมน์ในตารางวันที่ของคุณ เช่น เช้าบ่าย หรือ ชั่วโมง เป็นเขตข้อมูลวันที่ของเวลาในพื้นที่แถว คอลัมน์ หรือตัวกรองของ PivotTable
หมายเหตุ: วัน คือหน่วยเวลาที่น้อยที่สุดที่ฟังก์ชันตัวแสดงเวลา DAX สามารถทํางานได้ ถ้าคุณไม่จําเป็นต้องทํางานกับค่าเวลา คุณควรลดความแม่นยําของข้อมูลของคุณเพื่อใช้วันเป็นหน่วยต่ําสุด
ถ้าคุณต้องการรวมข้อมูลของคุณในระดับเวลา ตารางวันที่ของคุณจะต้องมีคอลัมน์วันที่ที่มีเวลารวมอยู่ด้วย อันที่จริงแล้วจะต้องมีคอลัมน์วันที่ที่มีหนึ่งแถวสําหรับทุกชั่วโมงหรือแม้แต่ทุกนาทีของทุกวันสําหรับทุกปีในช่วงวันที่ ทั้งนี้เนื่องจาก เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTimeKey ในตารางข้อเท็จจริงและคอลัมน์วันที่ในตารางวันที่ คุณต้องมีค่าที่ตรงกัน อย่างที่คุณสามารถจินตนาการได้ถ้าคุณรวมหลายปีสิ่งนี้สามารถทําให้เป็นตารางวันที่ขนาดใหญ่มาก
ในกรณีส่วนใหญ่ คุณต้องการรวมเฉพาะข้อมูลของคุณในแต่ละวันเท่านั้น กล่าวคือ คุณจะใช้คอลัมน์ เช่น ปี เดือน สัปดาห์ หรือ วันในสัปดาห์ เป็นเขตข้อมูลในพื้นที่แถว คอลัมน์ หรือตัวกรองของ PivotTable ในกรณีนี้ คอลัมน์วันที่ในตารางวันที่ต้องมีหนึ่งแถวสําหรับแต่ละวันในหนึ่งปีเท่านั้น ตามที่เราได้อธิบายไว้ก่อนหน้านี้
ถ้าคอลัมน์วันที่ของคุณมีระดับความแม่นยําของเวลา แต่คุณจะรวมในระดับวันเท่านั้น เพื่อสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงและตารางวันที่ คุณอาจต้องปรับเปลี่ยนตารางข้อเท็จจริงของคุณโดยการสร้างคอลัมน์ใหม่ที่ตัดค่าในคอลัมน์วันที่ให้เป็นค่าวัน กล่าวคือ แปลงค่า เช่น 19/10/2010 8:44:00AM เป็น 10/19/2010 12:00:00 AM จากนั้นคุณสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ใหม่นี้และคอลัมน์วันที่ในตารางวันที่ได้ เนื่องจากค่าตรงกัน
ลองมาดูตัวอย่างกัน รูปนี้แสดงคอลัมน์ DateTimeKey ในตารางข้อมูลการขาย การรวมข้อมูลในตารางนี้ทั้งหมดจะต้องเป็นระดับวันเท่านั้น โดยใช้คอลัมน์ในตารางวันที่ในปฏิทิน เช่น ปี เดือน ไตรมาส เป็นต้น เวลาที่รวมอยู่ในค่าไม่เกี่ยวข้อง แต่เป็นวันที่จริงเท่านั้น
เนื่องจากเราไม่จําเป็นต้องวิเคราะห์ข้อมูลนี้ในระดับเวลา เราจึงไม่จําเป็นต้องมีคอลัมน์ วันที่ ในตารางวันที่ในปฏิทิน เพื่อรวมหนึ่งแถวสําหรับทุกชั่วโมงและทุกนาทีของทุกวันในแต่ละปี ดังนั้น คอลัมน์ วันที่ ในตารางวันที่ของเรามีลักษณะดังนี้:
เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTimeKey ในตารางยอดขายและคอลัมน์วันที่ในตารางปฏิทิน เราสามารถสร้างคอลัมน์จากการคํานวณใหม่ในตารางข้อมูลการขาย และใช้ฟังก์ชัน TRUNC เพื่อตัดค่าวันที่และเวลาในคอลัมน์ DateTimeKey ให้เป็นค่าวันที่ที่ตรงกับค่าในคอลัมน์ วันที่ ในตารางปฏิทิน สูตรของเรามีลักษณะดังนี้:
=TRUNC([DateTimeKey],0)
ซึ่งทําให้เรามีคอลัมน์ใหม่ (เราตั้งชื่อว่า DateKey) ที่มีวันที่จากคอลัมน์ DateTimeKey และเวลา 12:00:00 น. สําหรับแต่ละแถว:
ตอนนี้เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ใหม่ (DateKey) และคอลัมน์วันที่ในตารางปฏิทินได้แล้ว
ในทํานองเดียวกัน เราสามารถสร้างคอลัมน์จากการคํานวณในตารางยอดขายที่ลดความแม่นยําของเวลาในคอลัมน์ DateTimeKey เป็นระดับชั่วโมงของความแม่นยํา ในกรณีนี้ ฟังก์ชัน TRUNC จะไม่ทํางาน แต่เรายังคงสามารถใช้ฟังก์ชันวันที่และเวลา DAX อื่นเพื่อแยกและเชื่อมค่าใหม่เข้ากับระดับความแม่นยําของชั่วโมง เราสามารถใช้สูตรดังนี้:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) ) + TIME (HOUR([DateTimeKey]), 0, 0)
คอลัมน์ใหม่ของเรามีลักษณะดังนี้:
คอลัมน์ วันที่ ในตารางวันที่มีค่าเป็นระดับความแม่นยําเป็นชั่วโมง จากนั้นเราสามารถสร้างความสัมพันธ์ระหว่างค่าเหล่านั้นได้
ทําให้วันที่ใช้ได้มากขึ้น
คอลัมน์วันที่หลายคอลัมน์ที่คุณสร้างในตารางวันที่ของคุณมีความจําเป็นสําหรับเขตข้อมูลอื่นๆ แต่ไม่ใช่ทั้งหมดที่มีประโยชน์ในการวิเคราะห์ ตัวอย่างเช่น เขตข้อมูล DateKey ในตารางยอดขายที่เราอ้างอิงและแสดงในบทความนี้มีความสําคัญเนื่องจากทุกทรานแซคชันนั้นจะถูกบันทึกตามวันที่และเวลาที่ระบุ แต่จากมุมมองการวิเคราะห์และการรายงาน ก็ไม่ได้เป็นประโยชน์ทั้งหมดเนื่องจากเราไม่สามารถใช้เป็นเขตข้อมูลแถว คอลัมน์ หรือตัวกรองใน Pivot Table หรือรายงานได้
ในทํานองเดียวกัน ในตัวอย่างของเรา คอลัมน์ วันที่ ในตารางปฏิทินจะมีประโยชน์มาก และมีความสําคัญมาก แต่คุณไม่สามารถใช้เป็นมิติใน PivotTable ได้
เมื่อต้องการเก็บตารางและคอลัมน์ไว้ในตารางให้เป็นประโยชน์มากที่สุด เท่าที่จะเป็นไปได้ และเพื่อทําให้รายการ เขตข้อมูลรายงาน PivotTable หรือ Power View ง่ายต่อการนําทาง จึงเป็นเรื่องสําคัญที่จะต้องซ่อนคอลัมน์ที่ไม่จําเป็นจากเครื่องมือไคลเอ็นต์ นอกจากนี้ คุณอาจต้องการซ่อนบางตารางด้วย ตารางวันหยุดที่แสดงก่อนหน้านี้มีวันที่วันหยุดที่สําคัญสําหรับบางคอลัมน์ในตารางปฏิทิน แต่คุณไม่สามารถใช้คอลัมน์วันที่และวันหยุดในตารางวันหยุดเป็นเขตข้อมูลใน PivotTable ได้ ที่นี่อีกครั้ง เพื่อทําให้นําทางรายการเขตข้อมูลได้ง่ายขึ้น คุณสามารถซ่อนตารางวันหยุดทั้งหมดได้
อีกแง่มุมที่สําคัญของการทํางานกับวันที่คือข้อตกลงการตั้งชื่อ คุณสามารถตั้งชื่อตารางและคอลัมน์ใน Power Pivot ได้ตามที่คุณต้องการ แต่ควรจําไว้ โดยเฉพาะอย่างยิ่ง ถ้าคุณจะแชร์เวิร์กบุ๊กของคุณกับผู้ใช้อื่น ข้อตกลงการตั้งชื่อที่ดีจะช่วยให้การระบุตารางและวันที่ทําได้ง่ายขึ้น ไม่เพียงแต่ในรายการเขตข้อมูล แต่ยังอยู่ใน Power Pivot และในสูตร DAX
หลังจากที่คุณมีตารางวันที่ในตัวแบบข้อมูลแล้ว คุณสามารถเริ่มสร้างการวัดที่จะช่วยให้คุณได้รับประโยชน์สูงสุดจากข้อมูลของคุณ บางยอดขายอาจง่ายพอๆ กับผลรวมของยอดขายสําหรับปีปัจจุบัน และยอดขายอื่นๆ อาจซับซ้อนกว่า ซึ่งคุณจําเป็นต้องกรองในช่วงของวันที่ที่ไม่ซ้ํากัน เรียนรู้เพิ่มเติมใน การวัดในฟังก์ชัน Power Pivot และ ตัวแสดงเวลา
ภาคผนวก
การแปลงวันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่
ในบางกรณี ตารางข้อเท็จจริงที่มีข้อมูลธุรกรรมอาจมีวันที่ของชนิดข้อมูลข้อความ กล่าวคือ วันที่ที่ปรากฏเป็น 2012-12-04T11:47:09 ไม่ใช่วันที่เลย หรืออย่างน้อยก็ไม่ใช่ชนิดของวันที่ที่ Power Pivot สามารถเข้าใจได้ มันเป็นเพียงข้อความที่อ่านเหมือนวันที่ เพื่อสร้างความสัมพันธ์ระหว่างคอลัมน์วันที่ในตารางข้อเท็จจริง และคอลัมน์วันที่ในตารางวันที่ ทั้งสองคอลัมน์ต้องเป็นชนิดข้อมูลวันที่
โดยทั่วไปแล้ว เมื่อคุณพยายามเปลี่ยนชนิดข้อมูลสําหรับคอลัมน์ของวันที่ที่เป็นชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่ Power Pivot สามารถแปลวันที่และแปลงเป็นชนิดข้อมูลวันที่จริงโดยอัตโนมัติ ถ้า Power Pivot ไม่สามารถทําการแปลงชนิดข้อมูลได้ คุณจะได้รับข้อผิดพลาดชนิดที่ไม่ตรงกัน
อย่างไรก็ตาม คุณยังสามารถแปลงวันที่เป็นชนิดข้อมูลวันที่จริงได้ คุณสามารถสร้างคอลัมน์จากการคํานวณใหม่และใช้สูตร DAX เพื่อแยกวิเคราะห์ปี เดือน วัน เวลา และอื่นๆ จากสตริงข้อความ แล้วเชื่อมเข้าด้วยกันในลักษณะที่ Power Pivot สามารถอ่านเป็นวันที่จริงได้
ในตัวอย่างนี้ เราได้นําเข้าตารางข้อเท็จจริงที่ชื่อว่า ยอดขาย ลงใน Power Pivot ซึ่งมีคอลัมน์ที่ชื่อว่า DateTime ค่าจะปรากฏดังนี้:
ถ้าเราดูที่ ชนิดข้อมูล ในกลุ่ม การจัดรูปแบบ แท็บ หน้าแรก ของ Power Pivot เราจะเห็นว่าเป็นชนิดข้อมูลข้อความ
เราไม่สามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTime และคอลัมน์วันที่ในตารางวันที่ของเราได้เนื่องจากชนิดข้อมูลไม่ตรงกัน หากเราพยายามเปลี่ยนชนิดข้อมูลเป็น วันที่ เราได้รับข้อผิดพลาดประเภทที่ไม่ตรงกัน:
ในกรณีนี้ Power Pivot ไม่สามารถแปลงชนิดข้อมูลจากข้อความเป็นวันที่ได้ เรายังคงสามารถใช้คอลัมน์นี้ได้ แต่เพื่อให้เป็นชนิดข้อมูลวันที่จริง เราจําเป็นต้องสร้างคอลัมน์ใหม่ที่แยกวิเคราะห์ข้อความและสร้างใหม่ลงในค่า Power Pivot สามารถสร้างชนิดข้อมูลวันที่ได้
โปรดจําไว้ว่าจากส่วน การทํางานกับเวลา ก่อนหน้าในบทความนี้ คุณควรแปลงวันที่ในตารางข้อเท็จจริงของคุณให้เป็นระดับความแม่นยําวัน เว้นแต่ว่าการวิเคราะห์ของคุณจําเป็น เราต้องการให้ค่าในคอลัมน์ใหม่ของเราอยู่ในระดับความแม่นยําของวัน (ไม่รวมเวลา) เราสามารถแปลงค่าในคอลัมน์ DateTime เป็นชนิดข้อมูลวันที่ และลบระดับเวลาความแม่นยําด้วยสูตรต่อไปนี้:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
ซึ่งจะทําให้เรามีคอลัมน์ใหม่ (ในกรณีนี้คือวันที่ที่มีชื่อ) Power Pivot จะตรวจหาค่าที่เป็นวันที่ และตั้งค่าชนิดข้อมูลให้เป็น วันที่ โดยอัตโนมัติ
ถ้าเราต้องการรักษาระดับความแม่นยําของเวลา เราเพียงแค่ขยายสูตรเพื่อรวมชั่วโมง นาที และวินาที
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
ตอนนี้เรามีคอลัมน์ วันที่ ของชนิดข้อมูล วันที่ เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์นั้นและคอลัมน์วันที่ในวันที่ได้
แหล่งข้อมูลเพิ่มเติม
การเริ่มต้นใช้งานด่วนt: เรียนรู้ข้อมูลพื้นฐานเกี่ยวกับ DAX ภายใน 30 นาที
